update with a joins and default value for non join

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 2000 Forums

Transact-SQL (2000)

update with a joins and default value for non join

Author

Topic

dgaylor
Yak Posting Veteran

54 Posts

Posted - 2003-05-08 : 12:19:41

I have two tables.

TableA TableB
---------------------
COL1 COL1
COL2 COL2
COL3 COL3
COL4
etc.

I want to update COL3 in TableA with the value of COL3 in TableB, joining the 2 table on COL1 and COL2, but if a join can't occur I want to set COL3 to a default value of '9999'. I have tried the following and it doens't work, and I would like to do this in one statement. Is it possible? Thanks.

update a
set col3 = case when a.col1 = b.col1 and a.col2 = b.col2 then b.col3 else '9999' end
from tablea a, tableb b

The syntax seems to be accepted but it never finishes.

Thanks for any help.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-05-08 : 12:39:56


update tablea
set col3 = coalesce(b.col2,'9999')
from
    tablea a
    left join tableb b
        on (a.col1=b.col1 and a.col2=b.col2)

Jay White
{0}

dgaylor
Yak Posting Veteran

54 Posts

Posted - 2003-05-08 : 15:45:14

Thanks, it worked perfectly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources