Question Locked

Set-based update query

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

In SQL SERVER 2005 I have 2 tables as follows:
table1_old
{
id smallint (PK)
status bit
}
table1_new
{
id smallint (pk)
status bit
}

table1_old's records are, in effect, a subset of table1_new's records. I want to update the status flag for each record in table1_new that's in table1_old with the status value in table1_old for the associated record.

Obviously you can use a cursor, but i'd rather learn how to do it in a set-based query (I think it's possible, but I'm not sure how to write the TSQL Statement)

Can someone show me how to do it and if there are good resources online that teach set-based querying principles and techniques, could you post the link?

Thank you very much,

Fahim.

3 replies

by gwass

what do you want to update your status flag to?

you could try the following

update table1_new set status_flag = 'what ever you want to add'
where table1_new.colum = table1_old.colum

its important to get the correct relationship between tables here otherwise your going to end up with a mess

or if you wanted to copy all the contents of the status flags from one tabel to another you could try the following

update temp1_new
set col_3 = (select col_3 from temp1_old where temp1_old.col_1 = temp1_new.col_1)

the above SQL looks updates the table temp1_new column col_3 with the contents of col_3 from the temp1_old table

does this make scense?

by starnamer

[pre]
update table1_new
set status=o.status
from table1_new n
join table1_old o
on n.id=o.id
[/pre]

I'm afraid I've been doing this for so long that I haven't used any reference docs apart from BOL (Books-OnLine in SQLserver) for some time. You might try looking at SQLServerCentral.com as their mailing has pointers to lots of useful things on both their own and other sites.

This discussion is closed to new replies.