March 8, 2012 at 4:54 am
Hi,
I'm writing a script that will update selected multiple rows of records I got from my EXCEPT select.
I wrote this:
UPDATE t1
SET t1.col1 = t2.col1, t1.col2 = t2.col2, t1.col3 = t2.col3, t1.col4 = t2.col4, ........
FROM t2
WHERE t2.ID = t1.ID
AND
EXISTS
(SELECT col1, col2, col3, col4, ...........
FROM t1
EXCEPT
SELECT col1, col2, col3, col4, ...........
FROM t2)
Unfortunately, it updates all the records. I am expecting to update only those records that were produced by the EXCEPT query.
How I will make this work?
Please help!
Thanks!
March 8, 2012 at 6:27 am
ladyblue1075 (3/8/2012)
Hi,I'm writing a script that will update selected multiple rows of records I got from my EXCEPT select.
I wrote this:
UPDATE t1
SET t1.col1 = t2.col1, t1.col2 = t2.col2, t1.col3 = t2.col3, t1.col4 = t2.col4, ........
FROM t2
WHERE t2.ID = t1.ID
AND
EXISTS
(SELECT col1, col2, col3, col4, ...........
FROM t1
EXCEPT
SELECT col1, col2, col3, col4, ...........
FROM t2)
Unfortunately, it updates all the records. I am expecting to update only those records that were produced by the EXCEPT query.
How I will make this work?
Please help!
Thanks!
There is no relationship between the t1 in the update part of your query and the t1 in the exists part. The same thing is true for the both t2's. The exists part effectively evaluates to true if at least one row exists that is in t1 that is not in t2. So what happens is that you could almost have written something like this:
UPDATE t1
SET t1.col1 = t2.col1, t1.col2 = t2.col2, t1.col3 = t2.col3, t1.col4 = t2.col4, ........
FROM t2
WHERE t2.ID = t1.ID
AND 1 = 1
and get the same result.
I would suggest you ditch the except construct and go for something like this:
UPDATE t1
SET t1.col1 = t2.col1, t1.col2 = t2.col2, t1.col3 = t2.col3, t1.col4 = t2.col4, ........
FROM t1
INNER JOIN t2 on (t2.ID = t1.ID)
WHERE not ((t2.col1 is null and t1.col1 is null) or t2.col1 = t1.col1)
or not ((t2.col2 is null and t1.col2 is null) or t2.col2 = t1.col2)
or not ((t2.col3 is null and t1.col3 is null) or t2.col3 = t1.col3)
or not ((t2.col4 is null and t1.col4 is null) or t2.col4 = t1.col4)
or ........
As you can see, i've used the T-SQL specific "UPDATE - FROM" syntax, which resembles the from clause of the select statement. I personally find this better readable as it clearly separates the join conditions from your selection criteria.
This statement tests -in the where-clause- whether the values of t1 are the same as those in the row t2 with the same ID and only if at least one column is found to be different, it updates all columns of the same t1 instance as it tested the values for, to the values found in the t2 row.
March 8, 2012 at 7:20 am
Try using a CROSS APPLY instead of the EXISTS.
UPDATE t1
SET t1.col1 = t2.col1, t1.col2 = t2.col2, t1.col3 = t2.col3, t1.col4 = t2.col4, ........
FROM t1
INNER JOIN t2
ON t2.ID = t1.ID
CROSS APPLY (
SELECT t1.col1, t1.col2, t1.col3, t1.col4, ...........
EXCEPT
SELECT t2.col1, t2.col2, t2.col3, t2.col4, ...........
) AS c
Notice that there is no FROM clause inside the CROSS APPLY. This ensures that the fields are coming from the main query.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 8, 2012 at 8:23 am
There is no relationship between the t1 in the update part of your query and the t1 in the exists part. The same thing is true for the both t2's. The exists part effectively evaluates to true if at least one row exists that is in t1 that is not in t2. So what happens is that you could almost have written something like this:UPDATE t1
SET t1.col1 = t2.col1, t1.col2 = t2.col2, t1.col3 = t2.col3, t1.col4 = t2.col4, ........
FROM t2
WHERE t2.ID = t1.ID
AND 1 = 1
and get the same result.
I would suggest you ditch the except construct and go for something like this:
UPDATE t1
SET t1.col1 = t2.col1, t1.col2 = t2.col2, t1.col3 = t2.col3, t1.col4 = t2.col4, ........
FROM t1
INNER JOIN t2 on (t2.ID = t1.ID)
WHERE not ((t2.col1 is null and t1.col1 is null) or t2.col1 = t1.col1)
or not ((t2.col2 is null and t1.col2 is null) or t2.col2 = t1.col2)
or not ((t2.col3 is null and t1.col3 is null) or t2.col3 = t1.col3)
or not ((t2.col4 is null and t1.col4 is null) or t2.col4 = t1.col4)
or ........
As you can see, i've used the T-SQL specific "UPDATE - FROM" syntax, which resembles the from clause of the select statement. I personally find this better readable as it clearly separates the join conditions from your selection criteria.
This statement tests -in the where-clause- whether the values of t1 are the same as those in the row t2 with the same ID and only if at least one column is found to be different, it updates all columns of the same t1 instance as it tested the values for, to the values found in the t2 row.
This make sense to me!
But I am actually looking for something that won't have to use a lot of OR's as there are a number of columns involve.
However, I have found a better solution and it worked as I expected. Never thought that this can be done!
I removed the FROM from the EXCEPT subquery and put alias on each fields.
UPDATE t1
SET t1.col1 = t2.col1, t1.col2 = t2.col2, t1.col3 = t2.col3, t1.col4 = t2.col4, ........
FROM t2
WHERE t2.ID = t1.ID
AND
EXISTS
(SELECT t1.col1, t1.col2, t1.col3, t1.col4, ...........
EXCEPT
SELECT t2.col1, t2.col2, t2.col3, t2.col4, ...........
)
March 8, 2012 at 8:33 am
drew.allen (3/8/2012)
Try using a CROSS APPLY instead of the EXISTS.
UPDATE t1
SET t1.col1 = t2.col1, t1.col2 = t2.col2, t1.col3 = t2.col3, t1.col4 = t2.col4, ........
FROM t1
INNER JOIN t2
ON t2.ID = t1.ID
CROSS APPLY (
SELECT t1.col1, t1.col2, t1.col3, t1.col4, ...........
EXCEPT
SELECT t2.col1, t2.col2, t2.col3, t2.col4, ...........
) AS c
Notice that there is no FROM clause inside the CROSS APPLY. This ensures that the fields are coming from the main query.
Drew
This makes sense!
Almost similar to the one I found.
I might be able to use this in the future.
Thanks for the input!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply