update ct
set ct.NumberOfRoomsAllowed = pt.NumberOfSubRooms
FROM Roomtypes ct 
INNER JOIN Roomtypes pt ON ct.ParentRoomTypeNumber = pt.RoomTypeNumber
WHERE ISNULL(ct.NumberOfRoomsAllowed, 0) = 0 AND 
ct.ParentRoomtypeNumber IN (
select rt1.RoomTypeNumber from roomtypes rt1 (nolock)
where (Select count(*) From roomtypes rt2 (nolock) where rt2.ParentRoomTypeNumber = rt1.RoomTypeNumber and rt2.containersubtype = 0) = 1 
)