CODEWITHSUNDEEP
sql
Charles
Charles

Reputation: 315

update a table but only non duplicate records

table A

name, street, city, state, zip
bill, 123 main, newcity, null, 77777
bill, 123 main, newcity, null, 77777
fred, 23 west, greattown, null, 12345
bob, 4 a St, nowhere, null, 34567

table B

name, street, city, st, zip
bill, 123 main, newcity, me, 77777
bill, 123 main, newcity, me, 77777
fred, 23 west, greattown, ny, 12345
bob, 4 a St, nowhere, wy, 34567

i want to do

update table A
set state = tB.st
from 
table A tA inner join table B tB
on (tA.name = tB.name and tA.street = tB.street)

but I dont want to update the 2 records "bill, 123 main, newcity, null, 77777".

How do I exclude those rows from the tables?

Thanks Charles

Upvotes: 0

Views: 136

Answers (3)

Andriy M
Andriy M

Reputation: 77657

In SQL Server 2005+, you could do something like this:

;
WITH A_counted AS (
  SELECT
    *,
    cnt = COUNT(*) OVER (PARTITION BY name, street, city, zip)
  FROM TableA
),   B_counted AS (
  SELECT
    *,
    cnt = COUNT(*) OVER (PARTITION BY name, street, city, zip)
  FROM TableB
)
UPDATE A_counted
SET state = B.state
FROM B_counted B
WHERE A_counted.name   = B.name,
  AND A_counted.street = B.street,
  AND A_counted.city   = B.city,
  AND A_counted.zip    = B.zip
  AND A_counted.cnt    = 1
  AND B.cnt            = 1

Upvotes: 1

Bauhaus
Bauhaus

Reputation: 509

You should just need to append 1 more conditional to your join:

update table A
set state = tB.st
from 
table A tA inner join table B tB
on (tA.name = tB.name and tA.street = tB.street and tA.name <> 'Bill')

This should exclude those two rows from even being seen by the update statement.

Upvotes: 0

Adam V
Adam V

Reputation: 6356

The way I see it is:

1) Create a temp table with the records in A that appear more than once.

2) Create a second temp table with all records in B that don't match records in the temp table from step 1.

3) Update all records from step 2.

Upvotes: 0

Related Questions