0

I'm unable to update a table using the following join on another table. Basically TableA has some null values in its Name column and I'm trying to add them from the Customer tables Name column, joining on the ID column.

UPDATE
    TableA
SET
    TableA.Name = Customer.Name
FROM
    TableA
INNER JOIN
    Customer ON Customer.ID = TableA.ID
WHERE
    TableA.Name <> Customer.Name

I've also tried it where the last line is:

TableA.Name = null
Improve this question

2 Answers 2

Reset to default
2 
UPDATE
    TableA
SET
    TableA.Name = Customer.Name
FROM
    TableA
INNER JOIN
    Customer ON Customer.ID = TableA.ID
WHERE
    TableA.Name is null

you can't do logical evaluation of null, so you gotta use is or is not, depending on your needs.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Great, that works. Do you know whey the query didn't work when I was using TableA.Name <> Customer.Name Also, it doesn't work when I try: WHERE NOT TableA.ID = Customer.ID
Yup, that's because SQL server doesn't evaluate null values in a useful fashion.
@Clay NULL is absence of value. How can you say something is equal to (or not equal to) the absence of value? It is unknown by definition, so equality and inequality comparisons also yield unknown (which in turn yield false, so no rows qualify). I don't think that's "not useful" I think that's "adhering to the ANSI standard."
0

Why have a WHERE clause at all? If you want the names to be equal based on the ID, just do this:

UPDATE
    TableA
SET
    TableA.Name = Customer.Name
FROM
    TableA
INNER JOIN
    Customer ON Customer.ID = TableA.ID

Or if you must have a WHERE clause for performance, it should be this:

WHERE TableA.Name IS NULL OR TableA.Name <> Customer.Name
Improve this answer

5 Comments

Because this will cause an update to ALL rows, not just the ones where the name is empty or different. That can be a lot of additional I/O, logging, and trigger work for nothing.
If it's a LOT, he should be using a batch-approach to updating anyway. This looks like a one-time operation, so performance is probably a secondary concern.
huh? If he's updating 20 rows in a 2 billion row table, I disagree entirely. In any case, even with a batch approach, your suggestion will still update a lot of rows that don't need to be updated.
Correct, I don't want to update records that already match the conditions. This query may need to be run numerous times.
@user1562497 Ok then, please see my edit, as Clay's answer only updates the NULLs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.