Solved

MS-SQL: UPDATE only equal column NOT overwrite???

Posted on 2004-09-02
9
461 Views
Last Modified: 2012-08-14
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
Comment
Question by:kvnsdr
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 17

Expert Comment

by:BillAn1
ID: 11961442
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
 
LVL 26

Expert Comment

by:Hilaire
ID: 11961450
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
 
LVL 17

Expert Comment

by:BillAn1
ID: 11961458
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 26

Expert Comment

by:Hilaire
ID: 11961579
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
 
LVL 1

Author Comment

by:kvnsdr
ID: 11965144
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
 
LVL 26

Accepted Solution

by:
Hilaire earned 62 total points
ID: 11965185
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
 
LVL 17

Expert Comment

by:BillAn1
ID: 11965266
can't improve on Hilaire's answer ....
0
 
LVL 1

Author Comment

by:kvnsdr
ID: 11965782
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
 
LVL 7

Assisted Solution

by:ChrisFretwell
ChrisFretwell earned 63 total points
ID: 11969106
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question