Where exists help - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Where exists help

Author

Topic

godspeedba
Yak Posting Veteran

90 Posts

Posted - 2009-06-18 : 07:11:29

Hi friends

I thought the below would only update records that existed in the @item_table.

I guess this wont work as it isnt calibrating the ids of the table instead it is setting every record of tb_owner_email.email_rejected to 0

UPDATE tb_owner_email
SET email_rejected = 0
WHERE EXISTS
(SELECT booking_id FROM @item_table)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-18 : 07:45:59

That's because you can always find some booking_id in the @item_table. I believe what you want is to find the booking_id in tbl_owner_email that also exists in @item_table.
Jim


UPDATE tb_owner_email
SET email_rejected = 0
WHERE  
  booking_id in  
(SELECT booking_id    
  FROM @item_table)

OR

UPDATE t1  
SET email_rejected = 0
FROM
 tb_owner_email t1 
inner join
 @item_table t2
on
  t1.bookingid_id = t2.booking_id

OR

UPDATE t1  
SET email_rejected = 0
FROM
 tb_owner_email t1 
WHERE EXISTS
  (select 1 from @item_table where booking_id = t1.booking_id)

godspeedba
Yak Posting Veteran

90 Posts

Posted - 2009-06-18 : 08:13:52

jimf thank you very much. SQLteam.com is best

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources