kvnsdr
asked on
MS-SQL: UPDATE only equal column NOT overwrite???
I have two UPDATE statements updating one table from two other look-up tables. The first update is fine, however the following updates overwrite the correct data with NULL. Both statements update the same column in table 1.....
Q. How do prevent multiple updates from overwriting each other???
UPDATE table1
SET col_2 = (SELECT col_2 FROM table2 t2 WHERE col_1 = t2.col_1)
UPDATE table1
SET col_2 = (SELECT col_2 FROM table3 t3 WHERE col_1 = t3.col_1))
Q. How do prevent multiple updates from overwriting each other???
UPDATE table1
SET col_2 = (SELECT col_2 FROM table2 t2 WHERE col_1 = t2.col_1)
UPDATE table1
SET col_2 = (SELECT col_2 FROM table3 t3 WHERE col_1 = t3.col_1))
Try this, it won't update if there's no matching row in t3 (your syntax returns a NULL)
UPDATE t1
SET col_2 = t2.col_2
FROM table1 t1 INNER JOIN table2 t2 ON t1.col_1 = t2.col_1
UPDATE t1
SET col_2 = t3.col_2
FROM table1 t1 INNER JOIN table3 t2 ON t1.col_1 = t3.col_1
UPDATE t1
SET col_2 = t2.col_2
FROM table1 t1 INNER JOIN table2 t2 ON t1.col_1 = t2.col_1
UPDATE t1
SET col_2 = t3.col_2
FROM table1 t1 INNER JOIN table3 t2 ON t1.col_1 = t3.col_1
BTW, are the NULLs because hte record in table2 is NULL, or is it because there is no record in table2?
if the latter, the more correct way to do the update is
update table1
set col_2 = table2.col_2
from table2
where table1.col_1 = table2.col_1
update table1
set col_2 = table3.col_2
from table3
where table1.col_1 = table3.col_1
if the latter, the more correct way to do the update is
update table1
set col_2 = table2.col_2
from table2
where table1.col_1 = table2.col_1
update table1
set col_2 = table3.col_2
from table3
where table1.col_1 = table3.col_1
This will perform the two updates in one shot.
This might help if you don't wan't to trap errors between the two updates and handle transactions
UPDATE t1
SET col_2 = coalesce(t3.col_2, t2.col_2)
FROM table1 t1
LEFT OUTER JOIN table2 t2 ON t1.col_1 = t2.col_1
LEFT OUTER JOIN table3 t3 ON t1.col_1 = t3.col_1
WHERE coalesce(t3.col_2, t2.col_2) is not NULL
HTH
Hilaire
This might help if you don't wan't to trap errors between the two updates and handle transactions
UPDATE t1
SET col_2 = coalesce(t3.col_2, t2.col_2)
FROM table1 t1
LEFT OUTER JOIN table2 t2 ON t1.col_1 = t2.col_1
LEFT OUTER JOIN table3 t3 ON t1.col_1 = t3.col_1
WHERE coalesce(t3.col_2, t2.col_2) is not NULL
HTH
Hilaire
ASKER
I used the following code from BillAn1 with the most success. Not sure how to work "coalesce(.value, 'No match found')" in the code.......
Q. How can I place a literal message like 'No Match Found' instead of the <NULL>???
update table1
set col_2 = table2.col_2
from table2
where table1.col_1 = table2.col_1
update table1
set col_2 = table3.col_2
from table3
where table1.col_1 = table3.col_1
Q. How can I place a literal message like 'No Match Found' instead of the <NULL>???
update table1
set col_2 = table2.col_2
from table2
where table1.col_1 = table2.col_1
update table1
set col_2 = table3.col_2
from table3
where table1.col_1 = table3.col_1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
can't improve on Hilaire's answer ....
ASKER
Excellent Hilaire...... I have one more column in table1 that needs the same treatment.............
Q. How can I use more that one SET in the same script???
Q. How can I use more that one SET in the same script???
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
UPDATE table1
SET col_2 = ISNULL ( (SELECT col_2 FROM table2 t2 WHERE col_1 = t2.col_1), col_2)
UPDATE table1
SET col_2 = ISNULL( (SELECT col_2 FROM table3 t3 WHERE col_1 = t3.col_1)), col_2)