Solved

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

Posted on 2004-09-02
9
457 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
 
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now