Solved

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

Posted on 2004-09-02
9
458 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
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.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
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 combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

948 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