Update Join Without Null 3

[Skittle]

The following statement updates my customer table code
column with a value from an updates table.

UPDATE customer
SET customer.code =
(SELECT updates.code
FROM updates
WHERE updates.code = customer.code)

The problem I have is that where there is no record in the updates table that corresponds to the customer table, I get
the customer column set to NULL. How can I avoid the null and leave the existing value in the customer table alone when there is no match between customer and updates?



Dazed and confused
(N+, MCAD)

 

[vongrunt]

Transform subquery to inner join:

UPDATE C
SET code = U.code
FROM customer C 
INNER JOIN updates U on C.code=U.code

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Pigster14]

What if in the Where Statement you added "AND Updates.Code Is Not Null" That way, it would not include the codes that are null in the Updates table and update Customers.

Good Luck!

 

[vongrunt]

That's the problem with subqueries; they always return something. Whatever you put inside a subquery, all rows will still get updated - either with found value or NULL. This is very similar - but not identical! - to UPDATE with LEFT OUTER JOIN.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[cognoscognos]

Try this:

UPDATE customer C
SET code =
(SELECT code
FROM updates U
WHERE U.code = C.code)
WHERE EXISTS (SELECT * FROM UPDATES U
WHERE code = C.code);

 

[vongrunt]

Funny thing: all queries we posted won't do anything.

Is there any other column (not "code") to join between these two tables?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Skittle]

Thanks for your help.



Dazed and confused
(N+, MCAD)

 

[Skittle]

yes I have it sorted...you are correct vongrunt.
In my original example I should have typed

UPDATE customer
SET customer.code =
(SELECT updates.code
FROM updates
WHERE updates.keycol = customer.keycol)

If you use a seperate field, things start to work.
I actually need it to run on another platform as well as SQL Server and the best one to do that is cognoscognos's method. It's accepted on my other platform as well.




Dazed and confused
(N+, MCAD)