DECLARE @Rooms TABLE
(
		location VARCHAR(100),
		CheckinDay VARCHAR(100),
		checkindate DATETIME,
		roomtype VARCHAR(100),
		roomrate VARCHAR(100),
		executiontime DATETIME,
		id_num INT
)
INSERT INTO @Rooms 
SELECT 'Palms', 'Saturday', '8/30/2008', 'Florida', '199', '8/15/2008', 1 UNION ALL
SELECT 'Palms', 'Saturday', '8/30/2008', 'Florida', 'SOLD', '8/8/2008', 2 UNION ALL
SELECT 'Palms', 'Saturday', '8/30/2008', 'Atrium', '239', '8/8/2008', 3 UNION ALL
SELECT 'Palms', 'Saturday', '8/30/2008', 'Atrium', '239', '8/15/2008', 4


UPDATE RA
SET roomrate = 'SOLD'
FROM @Rooms RA
INNER JOIN (SELECT * FROM @Rooms WHERE roomrate = 'SOLD') RS ON RS.location = RA.location
		AND RA.CheckinDay = RS.CheckinDay
		AND RA.checkindate = RS.checkindate
		AND RA.roomtype = RS.roomtype
		AND RA.executiontime > RS.executiontime
		
SELECT * FROM @Rooms

"There is only one difference between a dream and an aim. A dream requires soundless sleep to see, whereas an aim requires sleepless efforts to achieve..!!"

quote:

---

Originally posted by visakh16

Update t
SET t.roomrate ='SOLD'
FROM yourtable t
INNER JOIN (SELECT roomtype,MAX(executiontime) as maxdate
            FROM table
            WHERE roomrate='SOLD'
            GROUP BY roomtype)t1
on t1.roomtype=t.roomtype
and t.executiontime>t1.maxdate

---