I'm implementing change tracking for user data. Many changes are possible, so I've built a temp table (#ChangesToMake) to hold the basics and am joining that to retrieve details. When updating the Users table from the Change table, it's only updating one column for each UserID. I'm not sure how to make it update the other columns at the same time.
(Interestingly, if I replace the update u set [column]... lines with select *, it properly shows me all the changes I want to make, one on each line of the resulting dataset.)
Here's enough SQL to recreate my trouble. It's simplified, but reproducible.
create table #Users
(
UserID int identity,
Title varchar(6),
FirstName varchar(50),
MiddleName varchar(50),
LastName varchar(100),
Phone varchar(10),
Fax varchar(10),
Email varchar(100)
)
create table #ChangeSet
(
ChangeSetID int identity,
UserID int
)
create table #Change
(
ChangeID int identity,
ChangeSetID int,
ColName varchar(50),
OldValue varchar(100),
NewValue varchar(100)
)
create table #ChangesToMake
(
UserID int,
ChangeSetID int
)
--sample: user data:
insert into #Users (FirstName, LastName, Phone, Email)
values ('Alice', 'Smith', 1234567890, 'alice@tuta.com')
, ('Bob', 'Jones', 1234567890, 'bob@tuta.com')
, ('Charlie', 'Brown', 1234567890, 'charlie@tuta.com')
--sample: sets of changes:
insert into #ChangeSet
values (1), (2), (3)
--sample: details of changes:
insert into #Change
values (1, 'LastName', 'Smith', 'Smith-Wesson')
, (1, 'Fax', null, '1234567890')
, (2, 'FirstName', 'Bob','Barry')
, (2, 'MiddleName', null, 'X')
, (3, 'Title', null, 'Dr.')
, (3, 'Email', 'charlie@tuta.com', 'chuck@tuta.com')
--find sets of changes, per user:
insert into #ChangesToMake
select distinct UserID, ChangeSetID
from #ChangeSet x
--see sets of changes:
select *
from #ChangesToMake
order by UserID
--preview before:
select *
from #Users
where UserID in (select UserID from #ChangesToMake)
order by UserID
--update users (original):
update u
set Title = isnull(case when c.ColName='Title' then c.NewValue end, u.Title),
FirstName = isnull(case when c.ColName='FirstName' then c.NewValue end, u.FirstName),
MiddleName = isnull(case when c.ColName='MiddleName' then c.NewValue end, u.MiddleName),
LastName = isnull(case when c.ColName='LastName' then c.NewValue end, u.LastName),
Phone = isnull(case when c.ColName='Phone' then c.NewValue end, u.Phone),
Fax = isnull(case when c.ColName='Fax' then c.NewValue end, u.Fax),
Email = isnull(case when c.ColName='Email' then c.NewValue end, u.Email)
from #ChangesToMake x
inner join #Users u on u.UserID = x.UserID
inner join #Change c on c.ChangeSetID = x.ChangeSetID
--review after:
select *
from #Users
where UserID in (select UserID from #ChangesToMake)
order by UserID
I tried adapting Charlieface's pre-aggregate suggestion in the following way, but it also only made 1 change per UserID. Perhaps I misunderstood?
update u
set Title = isnull(case when c.CN='Title' then c.NV end, u.Title),
FirstName = isnull(case when c.CN='FirstName' then c.NV end, u.FirstName),
MiddleName = isnull(case when c.CN='MiddleName' then c.NV end, u.MiddleName),
LastName = isnull(case when c.CN='LastName' then c.NV end, u.LastName),
Phone = isnull(case when c.CN='Phone' then c.NV end, u.Phone),
Fax = isnull(case when c.CN='Fax' then c.NV end, u.Fax),
Email = isnull(case when c.CN='Email' then c.NV end, u.Email)
from #ChangesToMake x
cross apply (
select c1.ChangeSetID, cs1.UserID, max(ColName) as CN, max(NewValue) as NV
from #Change c1
inner join #ChangeSet cs1 on cs1.ChangeSetID = c1.ChangeSetID
group by c1.ChangeSetID, cs1.UserID, ColName
having ColName in ('Title', 'FirstName', 'MiddleName', 'LastName', 'Phone', 'Fax', 'Email')
and c1.ChangeSetID=x.ChangeSetID
) c
inner join #Users u on u.UserID = c.UserID
SQL Server makes only one (random?) of out all the changes I want:
UserID Title FirstName MiddleName LastName Phone Fax Email
------ ----- --------- ---------- -------- ---------- ---------- --------------
1 NULL Alice NULL Smith 1234567890 1234567890 alice@tuta.com
2 NULL Barry NULL Jones 1234567890 NULL bob@tuta.com
3 NULL Charlie NULL Brown 1234567890 NULL chuck@tuta.com
If I split the updates by column and update the Users table 7 separate times, it works fine. But that seems so dreadfully inefficient:
update u
set FirstName = isnull(c.NewValue, u.FirstName)
from #ChangesToMake x
inner join #Users u on u.UserID = x.UserID
inner join #Change c on c.ChangeSetID = x.ChangeSetID
where c.ColName='FirstName'
Does anyone know what detail I'm missing here?
The issue here, as noted in my linked answer, is that you need to update each User row exactly once. You cannot run the UPDATE multiple times per row.
But you aren't aggregating your apply properly. You need to aggregate it only by the PK of the User table, ie UserID.
Then just use max(case when to pivot it up into separate columns.
Also note that the having is better as a where, and that #ChangesToMake just duplicates #ChangeSet so can be removed.
update u
set Title = isnull(c.Title, u.Title),
FirstName = isnull(c.FirstName, u.FirstName),
MiddleName = isnull(c.MiddleName, u.MiddleName),
LastName = isnull(c.LastName, u.LastName),
Phone = isnull(c.Phone, u.Phone),
Fax = isnull(c.Fax, u.Fax),
Email = isnull(c.Email, u.Email)
from (
select
cs.UserID,
max(case when c.ColName = 'Title' then c.NewValue end) as Title,
max(case when c.ColName = 'FirstName' then c.NewValue end) as FirstName,
max(case when c.ColName = 'MiddleName' then c.NewValue end) as MiddleName,
max(case when c.ColName = 'LastName' then c.NewValue end) as LastName,
max(case when c.ColName = 'Phone' then c.NewValue end) as Phone,
max(case when c.ColName = 'Fax' then c.NewValue end) as Fax,
max(case when c.ColName = 'Email' then c.NewValue end) as Email
from #Change c
join #ChangeSet cs on cs.ChangeSetID = c.ChangeSetID
where c.ColName in ('Title', 'FirstName', 'MiddleName', 'LastName', 'Phone', 'Fax', 'Email')
group by cs.UserID
) c
inner join #Users u on u.UserID = c.UserID;