Update Statement Problem 1

[EnemyGateIsDown]

Hi guys,

I am trying to write an update statement which updates a single table but I need to reference another table in order to get the data I need. The query currently looks like:

update reservations, rooms set reservations.room_id = rooms.room_id 
where 
reservations.roomname = rooms.[Meeting Room]

However this query does not work.. I get the following error:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ','.

Can someone help me with the syntax here as I cant seem to find a syntax which works.

Any help is as always greatly appreciated.

Cheers,

Chris

 

[bborissov]

Update reservations set reservations.room_id = rooms.room_id
where reservations.roomname = rooms.[Meeting Room]

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[SQLDenis]

update re 
set re.room_id = ro.room_id
from  reservations re 
join rooms ro on re.roomname = ro.[Meeting Room]

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[EnemyGateIsDown]

Thanks SQLDennis.. thats just the ticket..