UPDATE with a composite key NOT IN

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)

UPDATE with a composite key NOT IN

Author

Topic

ecowarrior
Starting Member

3 Posts

Posted - 2010-12-10 : 09:10:51

Hi all, quick SQL question if you can point me in the right direction.

Simplified, the problem is this:

Table 1:
Column 1 (Primary Key)
Column 2 (Primary Key)
Column 3

Table 2:
Column 1 (Primary Key)
Column 2 (Primary Key)

So Column1 and Column2 are a composite key.

I want to do something like:

UPDATE Table1 SET Column3 = 1 WHERE (Column1, Column2) NOT IN (SELECT Column1, Column2 FROM Table2)

but I'm stumped how to do this in SQL Server without doing something like '...WHERE Column1 + ':' + Column2 NOT IN (SELECT Column1 + ':' + Column2 FROM ...)'.

Help? Thanks

bobmcclellan
Starting Member

46 Posts

Posted - 2010-12-10 : 09:22:22

something like this should do it...
Please "test" this...

Update t1.Col3 = 1
From Table1 t1
left join Table2 t2 on t1.Column1 = t2.Column2
and t1.Column2 = t2.Column2

where t2.Column1 is null

hth,
..bob

bobmcclellan
Starting Member

46 Posts

Posted - 2010-12-10 : 09:23:35

Typo...

Update t1.Col3 = 1
From Table1 t1
left join Table2 t2 on t1.Column1 = t2.Column1
and t1.Column2 = t2.Column2
where t2.Column1 is null

bobmcclellan
Starting Member

46 Posts

Posted - 2010-12-10 : 09:24:39

..again... typo...
Update Table1
set Col3 = 1
From Table1 t1
left join Table2 t2 on t1.Column1 = t2.Column2
and t1.Column2 = t2.Column2

where t2.Column1 is null

ecowarrior
Starting Member

3 Posts

Posted - 2010-12-10 : 09:27:15

Close enough Bob, thanks! It appears to be Update t1 SET t1.Col3 = 1 FROM t1..... which is kinda weird syntax but does the job!
Many thanks for your time!

ecowarrior
Starting Member

3 Posts

Posted - 2010-12-10 : 09:27:41

OK you beat me to it :)
Thanks again!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources