对表 1 中与表 2 不匹配的多个列的更新。如果匹配，我不想更新

Question

这是两个表。

table 1
id  acct    firstname   lastname
1   11  dave    smith
2   21  john    james
3   32  mike    jones
4   43  steve   alan

table 2
1   11  dave    simpson
2   21  karan   james
3   35  mike    jones
4   42  steve   lynn

输出我只想看看发生了什么变化(并且只有在不匹配的情况下才能更新(，不确定这是否可能是

id  acct    firstname   lastname
1                       simpson
2           karan
3   35  
4   42                  lynn```

        
    update #table1
        set acct= t.acct
        , firstname = t.firstname
        , lastname = t.lastname
        -- select *
        from #table2 t
        inner join #table1 a 
            on a.id = t.ID
        where not ((a.acct = case when a.acct != t.acct  then t.acct  else a.acct  end and
                    a.firstname = case when a.firstname != t.firstname then t.firstname else a.firstname end and
                    a.lastname = case when a.lastname!= t.lastname then t.lastname else a.lastname end
                ))

Answer 1

您可以使用和OUTPUT子句。。。。

https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-服务器-ver15

update #table1
    set acct= t.acct
    , firstname = t.firstname
    , lastname = t.lastname
output
    inserted.ID,  
    deleted.acct,  
    inserted.acct,  
    deleted.firstname,  
    inserted.firstname,  
    deleted.lastname,  
    inserted.lastname
    -- select *
    from #table2 t
    inner join #table1 a 
        on a.id = t.ID
    where not ((a.acct = case when a.acct != t.acct  then t.acct  else a.acct  end and
                a.firstname = case when a.firstname != t.firstname then t.firstname else a.firstname end and
                a.lastname = case when a.lastname!= t.lastname then t.lastname else a.lastname end
            ))

Answer 2

你似乎想"擦除"；表1中的数据与表2中的数据相同。

update table1
    set acct = (case when table2.acct = table1.acct then null else table2.act end),
        firstname = (case when table2.firstname = table1.firstname then null else table2.firstname end),
        lastname = (case when table2.lastname = table1.lastname then null else table2.lastname end)        
    from table2
    where table1.id = table2.id;

使用NULLIF():可以更简洁地表达这一点

update table1
    set acct = nullif(table2.acct, table1.acct),
        firstname = nullif(table2.firstname, table1.firstname),
        lastname = nullif(able2.lastname, table1.lastname)        
    from table2
    where table1.id = table2.id

Answer 3

如果您的帖子正朝着手动更改跟踪的方向发展，则有可能在一条合并语句中使用更改来更新目标表。可能是这样的：

merge into table2 t2 using (select * from table1) t1
on t2.id = t1.id
when not matched by target 
then
insert (id, acct, firstname, lastname) values (t1.id, t1.acct, t1.firstname, t1.lastname)
when matched 
and (t2.acct != t1.acct or (t2.acct is null and t1.acct is not null) or (t2.acct is not null and t1.acct is null)
or t2.firstname != t1.firstname or (t2.firstname is null and t1.firstname is not null) or (t2.firstname is not null and t1.firstname is null)
or t2.lastname != t1.lastname or (t2.lastname is null and t1.lastname is not null) or (t2.lastname is not null and t1.lastname is null)) 
then
update set t2.acct = t1.acct, t2.firstname = t1.firstname, t2.lastname = t1.lastname, t2.lastupdated = sysdatetime() --last column keeps the time of the last change registered
when not matched by source 
then
update set t2.lastupdated = sysdatetime(), t2.deleted = 1 --assuming there's a deleted column that you want to update

此语句使用id列连接表1和表2。如果id存在于一个表中，而不存在于另一个表，则它允许您更新或插入数据。您还可以有额外的条件(如检查其他列上的差异(，以便使用更多的属性来调整数据，如lastupdated或deleted信息。