July 8, 2009 at 8:30 pm
If I have two tables as follows:
A (ID, x, y)
and
B (ID, x, y)
where ID is a one-to-one PK relationship between the tables and B contains records which are either
1) the same as those in A, or
2) updates which need to be applied to A. Note that either x, y or both may be updated in B.
I want to UPDATE the fields in A only if they are different in B. I know that (ignoring the possibility of NULLs, for readability purposes) I can do it in two hits:
UPDATE A
SET x = B.x
FROM A JOIN B on A.ID = B.ID
WHERE A.x <> B.x
and
UPDATE A
SET y = B.y
FROM A JOIN B on A.ID = B.ID
WHERE A.y <> B.y
Is there any way (and benefit?) of doing it in one pass? I also want to retrieve a count of the number of updates performed for each field. By the way, my real-world requirement includes such tables with four fields to be updated and millions of rows, with clustered indexes on the ID fields.
Thanks for any input!
Phil
July 8, 2009 at 8:45 pm
Well you could do it in a single but the case statement would be ugly and I think it would be more costly in terms of CPU and IO than to just do it 4 times.. You can store @@ROWCOUNT after each one to get the number of rows updated..
CEWII
July 9, 2009 at 1:37 am
UPDATEa
SETa.x = CASE WHEN a.x b.x THEN b.x ELSE a.x END,
a.y = CASE WHEN a.y b.y THEN b.y ELSE a.y END
FROMTableA AS a
INNER JOINTableB AS b ON b.ID = a.ID
WHEREa.x b.x
OR a.y b.y
N 56°04'39.16"
E 12°55'05.25"
July 9, 2009 at 1:37 am
Something is very strange!
This post is duplicated 4 times, and cannot be deleted...
N 56°04'39.16"
E 12°55'05.25"
July 9, 2009 at 1:37 am
Something is very strange!
This post is duplicated 4 times, and cannot be deleted...
N 56°04'39.16"
E 12°55'05.25"
July 9, 2009 at 1:38 am
Something is very strange!
This post is duplicated 4 times, and cannot be deleted...
N 56°04'39.16"
E 12°55'05.25"
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply