-- ENSURE YOU BACKUP ALL TABLES BEFORE PERFORMING THIS ACTION --------

DECLARE @SiteId INT;
DECLARE @BuildingId INT;
DECLARE @BuildingName NVARCHAR(MAX);

-- CHANGE THE NUMBER TO THE DESIRED SITE ID
SET @SiteId = 1

-- CHANGE THE NUMBER TO THE DESIRED BUILDING ID
SET @BuildingId = 1

-- CHANGE THE TEXT IN 'Example Name' TO THE DESIRED BUILDING NAME
SET @BuildingName = 'Example Name' 

-----------------------------------------------------------------------
-------- UPDATES DIRECTORY FIRSTNAME TO ASSIGNED BUILDING NAME --------
-----------------------------------------------------------------------

UPDATE [dbo].[Residents]

SET Firstname = @BuildingName
FROM [dbo].[Residents] Res
INNER JOIN  [dbo].[Room] Rm
ON  Res.RoomID = Rm.RoomID
WHERE Rm.BuildingNumber = @BuildingId and Rm.SiteNumber = @SiteId

-------------------------------------------------------------------------------------
-- THIS WILL CHANGE ALL SURNAMES TO THE THEIR RESPECTIVE FLOOR AND BUILDING NUMBER --
-------------------------------------------------------------------------------------

UPDATE [dbo].[Residents]
SET Surname = ((Rm.FloorNumber * 100) + Rm.RoomNumber)
FROM [dbo].[Residents] Res
INNER JOIN  [dbo].[Room] Rm
ON  Res.RoomID = Rm.RoomID
WHERE Rm.FloorNumber != -1 and Rm.RoomNumber != -1 and Rm.BuildingNumber = @BuildingId and Rm.SiteNumber = @SiteId