0

I am trying to update a column of a table A with the values in table B column based on if Table A.col1 = TableB.Col1.

Problem: I overwrite TableA column value with Null if Col1 is not found in TableB.Col1.

My current query is

UPDATE [tableA]
SET col2 = (SELECT col2 FROM [tableB] WHERE [TableB].col1 = [TableA].col1)

How can I avoid this?

Ex: TableA

Col1 Col2
1    100
2    200
3    300

TableB

Col1 Col2
1    1000
3    3000

Resulting table should be:

Table A

Col1 Col2
1    1000
2    200
3    3000

But I get:

Col1 Col 2
1    1000
2    null
3    3000

Any ideas?

Improve this question

3 Answers 3

Reset to default
1

You could do:

UPDATE [tableA]
SET col2 = COALESCE(
             (SELECT col2 FROM [tableB] WHERE [TableB].col1 = [TableA].col1),
             col2)

COALESCE returns the first non-NULL expression among its arguments.

Or, you could do:

UPDATE a
SET col2 = b.col2
FROM TableA a
    INNER JOIN
     TableB b
        ON
            a.col1 = b.col1

but you should be aware that this second form is SQL Server dialect, not standard SQL.

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

1 Comment

fantastic, thanks for the answer, used the 1st one, it worked a treat.
1

You don't want to update the whole table so your query needs a where clause. In this case :

WHERE exists (select 1 
                from [tableB] 
                where [TableB].col1=[TableA].col1 
                  and [TableB].col2 is not NULL -- that condition may or may not be needed 
             )
Improve this answer

Comments

0

This should do it, no?

UPDATE [tableA]
SET col2= (select col2 from [tableB] where [TableB].col1=[TableA].col1 and [TableB].col1 IS NOT NULL )
Improve this answer

1 Comment

No that doesnt seem to work. All values not in table B are now null in table A. Did you test it your side?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.