IF NOT EXISTS(select * from RESERVATIONMAIN (NOLOCK) where ReferenceNumber = -4)
	INSERT INTO RESERVATIONMAIN (ReferenceNumber, HOTELNUMBER, ReservationType) 
	VALUES (-4,0,0)

GO

IF NOT EXISTS(select * from RESERVATIONS (NOLOCK) where ReferenceNumber = -4)
	INSERT INTO RESERVATIONS (ReferenceNumber, GuestNumber, Status) 
	VALUES (-4,0,0)

GO

IF NOT EXISTS(select * from RESERVATIONADDRESS (NOLOCK) where AddressNumber = -4)
	INSERT INTO RESERVATIONADDRESS (AddressNumber, ADDRESSTYPE, LASTNAME)
	VAlues (-4,1, 'Occupied')
	
GO

IF NOT EXISTS(select * from RESERVATIONADDRESSXTAB (NOLOCK) where ReferenceNumber = -4)
	INSERT INTO RESERVATIONADDRESSXTAB (REFERENCENUMBER, GUESTNUMBER, ADDRESSNUMBER, AddressTypeNumber)
	VALUES (-4, 0, -4, 1)
	
GO

update RESERVATIONROOMLINEXTAB
Set ReferenceNumber = -4
from RESERVATIONROOMLINEXTAB rlx 
inner join RESERVATIONROOMLINE rl on rlx.LINENUMBER = rl.LINENUMBER and rlx.ReferenceNumber = -3
inner join rooms ro on rl.RoomNumber = ro.RoomNumber and ISNULL(ro.parentroomnumber, 0) > 0
WHERE exists (select * from RESERVATIONROOMLINEXTAB rlx2 
inner join RESERVATIONROOMLINE rl2 on rlx2.LINENUMBER = rl2.LINENUMBER and rlx2.ReferenceNumber = 0
inner join rooms ro2 on rl2.RoomNumber = ro2.RoomNumber and ISNULL(ro2.roomnumber, 0) = ro.ParentRoomNumber
AND rl2.ArrivalDate >= rl.ArrivalDate and rl2.DepartureDate <= rl.DepartureDate)