CODEWITHSUNDEEP
sql-serverssis
Kazmi
Kazmi

Reputation: 33

SQL Compare Two Tables and Update one of them

I have two Tables, A, B. A has two columns A1 and A2. B has two columns B1 and B2. In my case Some of the value in A2 and B2 are same, for such cases I want to update A1 = B1. So A1 should take the value from B1 when A2 = B2. I have written the following query but it makes all the values NULL in table A. Can anyone help me fix this?

Update A
SET A.A1 = (Select B1 from B Where A.A2 = B.B2)

Upvotes: 0

Views: 15202

Answers (2)

way0utwest
way0utwest

Reputation: 654

I like the inner join approach from Tim Biegeleisen. However, I would also use this to test by issuing a SELECT first.

select
  a.ai
, b.bi
, a.a2
, b.b2
 from a inner join b
 on a.a2 = b.b2

Examine to be sure the the values you are updating (col1) with new values (col2) are correct for the 3rd and 4th columns.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

You could use an INNER JOIN to identify which records should be updated:

UPDATE A
SET A.A1 = B.B1
FROM A
INNER JOIN B
    ON A.A2 = B.B2

The nice thing about this approach logically is that the only records which will get updated are the ones which survive the INNER JOIN.

Upvotes: 9

Related Questions