pair subselect in update query

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)

pair subselect in update query

Author

Topic

GustiX
Starting Member

28 Posts

Posted - 2009-06-29 : 07:38:37

Hi

I am trying to construct a query that seems pretty simple in my mind but
doesn't seem to be possible in sql....

What I would like to to is:

UPDATE
TABLE_A
SET
VALUE = VALUE*2 (doesn't really matter)
WHERE
X,Y in (SELECT X,Y FROM TABLE_B WHERE VALUE < 10)

but this is not allowed. I know I can use some hacks like
casting x+y into a varchar or something but there must be
a better way!

Using

WHERE
X in (select x from TABLE_B WHERE VALUE < 10)
AND Y in (select y from TABLE_B WHERE VALUE < 10)

doesn't give the correct result set.

HELP!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 07:50:55

[code]UPDATE TABLE_A
SET VALUE = VALUE * 2
WHERE EXISTS (SELECT X, Y FROM TABLE_B WHERE TABLE_B.X = TABLE_A.X AND TABLE_B.Y = TABLE_A.Y)
WHERE VALUE < 10 [/code]

N 56°04'39.26"
E 12°55'05.63"

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-29 : 07:51:55

UPDATE a
SET VALUE = VALUE * 2
FROM
TABLE_A a
INNER JOIN
TABLE_B b
ON
a.X = b.X
and a.Y = b.Y
WHERE b.VALUE < 10

Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources