CODEWITHSUNDEEP
sqlsql-server
James Verdune
James Verdune

Reputation: 101

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

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:

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?

Upvotes: 0

Views: 101

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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

Upvotes: 2

Related Questions