How to update a table based on unusual criteria? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to update a table based on unusual criteria?

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2009-09-30 : 00:01:25

Hello All,

I need to update Table_B setting value = 0, if record_id is not found in Table_A. Does anyone know how to accomplish this task? An example is shown below. Please advice.

Table_A
Record_ID First_Name Age value
2 Chris 21 NULL
3 Sue 92 NULL
4 Tony 39 NULL

Table_B
Record_ID First_Name Age value
1 Mike 34 NULL
2 Chris 21 NULL
4 Tony 39 NULL
6 Sara 19 NULL

Result
Record_ID First_Name Age value
1 Mike 34 0
2 Chris 21 NULL
4 Tony 39 NULL
6 Sara 19 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-30 : 00:11:25

[code]
UPDATE b
SET b.Value=0
FROM Table_B b
LEFT JOIN Table_A a
ON a.Record_ID=b.Record_ID
WHERE a.Record_ID IS NULL
[/code]

pras2007
Posting Yak Master

216 Posts

Posted - 2009-09-30 : 05:45:45

Thanks for the response visakh16, if I also want to update value = 1 where record_id from table_a and table_B are equal will i have to write a seperate query or modify the current one? Please advice.

Thanks.

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-09-30 : 05:55:09

UPDATE b
SET b.Value=(CASE WHEN a.Record_ID IS NULL THEN 0 ELSE 1 END )
FROM Table_B b
LEFT JOIN Table_A a
ON a.Record_ID=b.Record_ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources