December 15, 2003 at 2:07 pm
I am trying to accomplish a multiple column update using a semi-join but it seems that i cannot mimic oracle in this one.
UPDATE a
SET (a,b,c,d) = (
SELECT a,b,c,d from b
WHERE a.id = b.id
)
WHERE EXISTS (
SELECT 1 from b
WHERE a.id = b.id
)
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '('.
Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'WHERE'.
What are the alternatives?
Thanks
Oscar
Edited by - ozamora on 12/15/2003 2:07:39 PM
December 15, 2003 at 2:26 pm
Join update did the trick
UPDATE a
SET a = b.a,
b = b.b,
c = b.c,
d = b.d
FROM a,b
WHERE a.id = b.id
December 16, 2003 at 1:49 am
quote:
UPDATE aSET a = b.a,
b = b.b,
c = b.c,
d = b.d
FROM a,b
WHERE a.id = b.id
You need to check the query plan for this. I've just been working on a database that uses the WHERE clause to specify the join criteria and have noticed that the query cost is substantially higher.
Try the following and see if there is any performance difference.
UPDATE a
SET a = b.a,
b = b.b,
c = b.c,
d = b.d
FROM a INNER JOIN b ON A.Id = B.Id
========================
He was not wholly unware of the potential lack of insignificance.
December 16, 2003 at 10:36 am
TRY UPDATE FROM as follow:
UPDATE a
SET c1 = b.c1
FROM b
INNER JOIN a
ON a.c1 = b.c1
WHERE b.c2 = 'some condition'
AND a.c2 = 'some condition'
Edited by - mbenothmane on 12/16/2003 10:36:51 AM
December 17, 2003 at 8:11 am
quote:
Oracle mimicry
is quite fun ,
Here you have not specified the table for alis “A”
Just go through ANSI 92 SQL Update ,
But T-SQL Update is !000 Times better ,
Subqueries are not at all required in T-SQL updates
December 17, 2003 at 8:21 am
SQL92 and T-SQl give the same explain plan. Nested loop against the driving table (which is b).
Thanks all for your help
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply