[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 470
  • Last Modified:

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))
0
kvnsdr
Asked:
kvnsdr
  • 3
  • 3
  • 2
  • +1
2 Solutions
 
BillAn1Commented:
if you don't want it to happen if NULL, try this :

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)
0
 
HilaireCommented:
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

0
 
BillAn1Commented:
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
HilaireCommented:
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
0
 
kvnsdrAuthor Commented:
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
0
 
HilaireCommented:
UPDATE t1
SET col_2 = coalesce(t3.col_2, t2.col_2, 'No match found')
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
0
 
BillAn1Commented:
can't improve on Hilaire's answer ....
0
 
kvnsdrAuthor Commented:
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???
0
 
ChrisFretwellCommented:
You just put a comma between the values you want to update

UPDATE t1
SET col_2 = coalesce(t3.col_2, t2.col_2, 'No match found'), col_3 = coalesce(t3.col_3, t2.col_3, 'No match found')
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
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now