sql-serversql-updateinner-joinmultiple-tables

In Sql Server, how can I update Table A record values from multiple records of Table B


TableA: ID varchar, Name1 varchar, Name2 varchar

TableB: ID varchar, Name varchar, Type varchar

I want to update Name1 and Name2 in TableA from TableB when they have the same ID value: when TableB.Type = '1', then only update TableA.Name1 with TableB.Name; when TableB.Type = '2', then only update TableA.Name2 with TableB.Name;

I tried something like below but seems only Name1 get updated:

update TableA
set TableA.Name1 = case when TableB.Type = '1' then TableB.Name end,
TableA.Name2 = case when TableB.Type = '2' then TableB.Name end
from TableA join TableB on TableA.ID = Table B.ID

Your comments would be appreciated.


below is a runnable example to describe the case:

create table #AAA
(
    ID      varchar(1000),  
    Name1   varchar(1000),
    Name2   varchar(1000)
)
insert #AAA values ('99', null, null);

create table #BBB
(
    ID      varchar(1000),  
    Name    varchar(1000),
    Type    varchar(1000)
)
insert #BBB values ('99', 'x', '1');
insert #BBB values ('99', 'y', '2');

update a
set a.Name1 = case when b.Type = '1' then b.Name else a.Name1 end,
    a.Name2 = case when b.Type = '2' then b.Name else a.Name2 end
from #AAA a inner join #BBB b on a.ID = b.ID

select * from #AAA

*before the update, the data in #AAA is as below: ID=99; Name1=null; Name2=null;

*after the update, the data in #AAA is as below: ID=99; Name1=x; Name2=null;


Solution

  • I'm thinking of doing conditional aggregation first on the B table, followed by an update join:

    WITH cte AS (
        SELECT ID, MAX(CASE WHEN Type = '1' THEN Name END) AS Name1,
                   MAX(CASE WHEN Type = '2' THEN Name END) AS Name2
        FROM TableB
        GROUP BY ID
    )
    
    UPDATE a
    SET Name1 = b.Name1, Name2 = b.Name2
    FROM TableA a
    INNER JOIN cte b ON a.ID = B.ID;