Solved

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

Posted on 2004-09-02
9
463 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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 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

Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

690 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