CREATE PROCEDURE dbo.FixupUnitIds
	@building_id INT
AS

	BEGIN TRY  -- exception handling.
		BEGIN TRAN -- do it in a transaction, so all of the operations will succeed or fail together.
		
		DECLARE @highest_unit_id INT;
		-- find the highest unit id
		SELECT @highest_unit_id = MAX(UnitId) FROM Units WHERE BuildingId = @building_id; 
		
		-- update the four tables
		UPDATE Meetings SET UnitID = @highest_unit_id WHERE BuildingId = @building_id;
		UPDATE DocumentAssociations SET UnitID = @highest_unit_id WHERE BuildingId = @building_id;
		UPDATE UnitProducts  SET UnitID = @highest_unit_id WHERE BuildingId = @building_id;
		UPDATE UnitContacts  SET UnitID = @highest_unit_id WHERE BuildingId = @building_id;
		
		-- delete all except the highest unit id
		DELETE FROM Units WHERE UnitId <> @highest_unit_id AND BuildingId = @building_id;
		
		COMMIT TRAN
	END TRY
	BEGIN CATCH
		ROLLBACK TRAN;
		SELECT
			ERROR_NUMBER() AS ErrorNumber,
			ERROR_SEVERITY() AS ErrorSeverity,
			ERROR_STATE() AS ErrorState,
			ERROR_PROCEDURE() AS ErrorProcedure,
			ERROR_LINE() AS ErrorLine,
			ERROR_MESSAGE() AS ErrorMessage
	END CATCH

GO

exec dbo.sp_ConvertMFtoSF 3422 -- assuming 3422 is the building id.

CREATE PROCEDURE dbo.sp_ConvertMFtoSF
	@bldg_id INT
AS
BEGIN TRY 
	BEGIN TRAN 

		SELECT UnitID, BuildingID
		FROM dbo.Units
		WHERE BuildingID = @bldg_id
		--	 GO	

		DECLARE @first_unit_id INT;
		-- find the oldest unit id
		SELECT @first_unit_id = MIN(UnitId) FROM UnitBuildingView;

		-- update the tables
		UPDATE [dbo].[DocumentAssociations] SET UnitID = @first_unit_id WHERE BuildingId = @bldg_id;
		UPDATE [dbo].[Buildings] SET BuildingUseType = 'SingleFamily', NumberUniits = '1' WHERE BuildingId = @bldg_id;

		-- delete all except the highest unit id
		DELETE FROM [dbo].[Units] WHERE UnitId <> @first_unit_id AND BuildingId = @bldg_id;

	COMMIT TRAN
END TRY
BEGIN CATCH
	 ROLLBACK TRAN;
	 SELECT
	 ERROR_NUMBER() AS ErrorNumber,
	 ERROR_SEVERITY() AS ErrorSeverity,
	 ERROR_STATE() AS ErrorState,
	 ERROR_PROCEDURE() AS ErrorProcedure,
	 ERROR_LINE() AS ErrorLine,
	 ERROR_MESSAGE() AS ErrorMessage
END CATCH

GO