In SQL Server, how do you update table from another table?

Question

Here are my two tables:

Table1 +-----------+-----------+-------+-------+----------+ | Address1 | Address2 | Unit1 | Unit2 | Precinct | +-----------+-----------+-------+-------+----------+ | 150 | Oak St | 0 | 10 | 1111 | +-----------+-----------+-------+-------+----------+ Table2 +-----------+-----------+-------+-------+----------+ | Address1 | Address2 | Unit1 | Unit2 | Precinct | +-----------+-----------+-------+-------+----------+ | 150 | Oak St | 0 | 10 | 1111 | | 150 | Oak St | 0 | 10 | 2222 | +-----------+-----------+-------+-------+----------+

What I'm trying to do is copy date from Table2 (source) over to Table1 (target) like this:

Match on Address1, Address2, Unit1, Unit2
if precinct is the same, don't do anything
if precinct is different, append with a comma ("1111" to "1111, 2222")

This is what I tried:

UPDATE t1 SET t1.Precinct = CASE WHEN t1.Precinct LIKE '%' + t2.Precinct + '%' THEN t1.Precinct ELSE t1.Precinct + ',' + t2.Precinct END FROM Table1 t1 join Table2 t2 on t1.Address1 = t2.Address1 and t1.Address2 = t2.Address2 and t1.Unit1 = t2.Unit1 and t1.Unit2 = t2.Unit2

My expected result is this:

Expected Result (Table1) +-----------+-----------+-------+-------+-----------+ | Address1 | Address2 | Unit1 | Unit2 | Precinct | +-----------+-----------+-------+-------+-----------+ | 150 | Oak St | 0 | 10 | 1111,2222 | +-----------+-----------+-------+-------+-----------+

but what I get is this:

Actual Result (Table1) (Nothing changes) +-----------+-----------+-------+-------+-----------+ | Address1 | Address2 | Unit1 | Unit2 | Precinct | +-----------+-----------+-------+-------+-----------+ | 150 | Oak St | 0 | 10 | 1111 | +-----------+-----------+-------+-------+-----------+

I played around with it a little bit and found out that since the match on (address1, address2, unit1, unit2) has 2 results in the Source table, the query only ends up looking at the first row (precinct=1111) and never looking at the second row.

How can I work around this? Is my current approach the complete wrong approach?

You need to group Table2 by the fields other than Precinct. You could use string_agg to get the Precinct value. — Igor
– Igor, Commented Aug 21, 2020 at 20:35
Is there a way to do it without string_agg? Unfortunately there is some extra logic I wanted to add to the WHEN clause that I left out for simplicity's purposes. In my app the Precint column is JSON, so I would be adding to that. — James Verdune
– James Verdune, Commented Aug 21, 2020 at 20:40
It is an art, but please don't dumb down your problem. If the question you ask is simpler than the problem you have, people waste time on the wrong answers. — Aaron Bertrand
– Aaron Bertrand, Commented Aug 21, 2020 at 20:41
Ah, will keep that in mind! Don't have much experience on stackoverflow (posting, at least), so still learning. — James Verdune
– James Verdune, Commented Aug 21, 2020 at 21:33

Gordon Linoff · Accepted Answer · 2020-08-21 21:20:06Z

This isn't going to work. First, storing values in comma-delimited lists is strongly discouraged. But if you must do this, you need to aggregate table2 first. An update updates a row only once.

So:

UPDATE t1 SET t1.Precinct = t1.Precinct + ',' + t2.Precincts FROM Table1 t1 JOIN (SELECT address1, address2, unit1, unit2, STRING_AGG(Precinct, ',') as precincts FROM Table2 t2 WHERE NOT EXISTS (SELECT 1 FROM table1 t1 WHERE t1.Address1 = t2.Address1 AND t1.Address2 = t2.Address2 AND t1.Unit1 = t2.Unit1 AND t1.Unit2 = t2.Unit2 AND ',' + t1.precinct + ',' LIKE '%,' + t2.precinct + ',%' ) GROUP BY address1, address2, unit1, unit2 ) t2 ON t1.Address1 = t2.Address1 AND t1.Address2 = t2.Address2 AND t1.Unit1 = t2.Unit1 AND t1.Unit2 = t2.Unit2

Collectives™ on Stack Overflow

In SQL Server, how do you update table from another table?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related