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;
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;