Update a table using info from an other table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update a table using info from an other table

Author

Topic

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2009-02-05 : 16:43:37

I need to update a table by using info from another table.

Permanent table has inncode, hotelname, and active

Temp table has inncode, hotelname

I need to update the permanent table active column to 1 where permanent inncode = temp inncode. I am getting an error from this query

UPDATE MeetingExcellence_Hotels
   SET excellenceHotel = 1
      
WHERE (SELECT 	DISTINCT 
	 MH.Inncode, 
    MT.inncode AS TEMPINNCODE,
    MH.Hotel, 
	 MT.hotel AS TEMPHOTEL,
MH.EXCELLENCEHOTEL 
   
FROM
    MeetingExcellence_Hotels AS MH 
INNER JOIN
    MeetingExcellenceTEMP AS MT ON MT.inncode = MH.Inncode
WHERE MH.EXCELLENCEHOTEL = 0)

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-05 : 17:03:05

Won't this be enough?

UPDATE MH
   SET MH.excellenceHotel = 1
   From MeetingExcellence_Hotels AS MH
   Inner Join MeetingExcellenceTEMP AS MT 
   ON MT.inncode = MH.Inncode

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2009-02-05 : 17:07:17

That works!! I was over analyzing it. Thank you

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-05 : 23:05:48

use any of these if u want UPDATE MH
SET MH.excellenceHotel = 1
From MeetingExcellence_Hotels AS MH
WHERE EXISTS (select * from MeetingExcellenceTEMP where inncode = MH.Inncode)

UPDATE MH
SET MH.excellenceHotel = 1
From MeetingExcellence_Hotels AS MH
WHERE Inncode IN (select inncode from MeetingExcellenceTEMP)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources