sql server, updating a table according to a second table 2

[pssheba]

Hi
my table contains 10000 lines.
my second table contains 8000.
i want to update 1st table's column "present" based on second table.
i a person i 1st exists in 2nd table, 1st's "present" will get 1, else = 0.
the code should be like:

select a.present, b.name
from
first as a full join second as b
if exists a.name=b.name then a.present=1 else a.present=0

surely the last row is wrong. can i update a table inside a select query ? I really dont know how to approach it.
i wish someone could help me with that..
Thanks

 

[SQLDenis]

something like this...untested

update a set a.present = case a.name when null then 0 else 1 end
from a left join b on a.name=b.name

or in 2 statements

update a set present=1
where exists (select * from b where a.name = b.name)

update a set present=0
where not  exists (select * from b where a.name = b.name)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[philhege]

UPDATE first
set present = 1
WHERE name in (SELECT Name FROM Second)

(To the logic of the thing, what if you have more than one Joan Smith?)

There's more esoteric code for SQL Server (see UPDATE in BOL), but the statement above is portable to RDBM systems that can handle large sub-query results

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[pssheba]

Thanks a lot !