具有多个条件的MS SQL更新表

Been reading this site for answers for quite a while and now asking my first question!

一直在阅读这个网站寻找答案，现在问我的第一个问题！

I'm using SQL Server

我正在使用SQL Server

I have two tables, ABC and ABC_Temp.

我有两个表，ABC和ABC_Temp。

The contents are inserted into the ABC_Temp first before making its way to ABC. Table ABC and ABC_Temp have the same columns, except that ABC_Temp has an extra column called LastUpdatedDate, which contains the date of the last update. Because ABC_Temp can have more than 1 of the same record, it has a composite key of the item number and the last updated date.

在进入ABC之前，内容首先被插入ABC_Temp。表ABC和ABC_Temp具有相同的列，除了ABC_Temp有一个名为LastUpdatedDate的额外列，其中包含上次更新的日期。因为ABC_Temp可以有多个相同的记录，所以它具有项目编号和最后更新日期的复合键。

The columns are: ItemNo | Price | Qty and ABC_Temp has an extra column: LastUpdatedDate

列是：ItemNo |价格| Qty和ABC_Temp有一个额外的列：LastUpdatedDate

I want to create a statement that follows the following conditions:

我想创建一个遵循以下条件的语句：

Check if each of the attributes of ABC differ from the value of ABC_Temp for records with the same key, if so then do the update (Even if only one attribute is different, all other attributes can be updated as well)
检查ABC的每个属性是否与具有相同键的记录的ABC_Temp值不同，如果是，则执行更新（即使只有一个属性不同，所有其他属性也可以更新）
Only update those that need changes, if the record is the same, then it would not update.
只更新那些需要更改的内容，如果记录相同，则不会更新。
Since an item can have more than one record in ABC_Temp I only want the latest updated one to be updated to ABC
由于一个项目在ABC_Temp中可以有多个记录，我只希望将最新更新的记录更新为ABC

I am currently using 2005 (I think, not at work at the moment).

我目前正在使用2005年（我想，现在不在工作）。

This will be in a stored procedure and is called inside the VBscript scheduled task. So I believe it is a once time thing. Also I'm not trying to sync the two tables, as the contents of ABC_Temp would only contain new records bulk inserted from a text file through BCP. For the sake of context, this will be used with in conjunction with an insert stored proc that checks if records exist.

这将在存储过程中，并在VBscript计划任务内部调用。所以我相信这是一次性的事情。此外，我不是要同步这两个表，因为ABC_Temp的内容只包含通过BCP从文本文件中批量插入的新记录。为了上下文，这将与插入存储过程结合使用，以检查是否存在记录。

2 个解决方案

#1

UPDATE
    ABC
SET
    price = T1.price,
    qty = T1.qty
FROM
    ABC
INNER JOIN ABC_Temp T1 ON
    T1.item_no = ABC.item_no
LEFT OUTER JOIN ABC_Temp T2 ON
    T2.item_no = T1.item_no AND
    T2.last_updated_date > T1.last_updated_date
WHERE
    T2.item_no IS NULL AND
    (
        T1.price <> ABC.price OR
        T1.qty <> ABC.qty
    )

If NULL values are possible in the price or qty columns then you will need to account for that. In this case I would probably change the inequality statements to look like this:

如果在price或qty列中可以使用NULL值，那么您需要考虑到这一点。在这种情况下，我可能会将不等式语句更改为：

COALESCE(T1.price, -1) <> COALESCE(ABC.price, -1)

This assumes that -1 is not a valid value in the data, so you don't have to worry about it actually appearing there.

这假设-1不是数据中的有效值，因此您不必担心它实际出现在那里。

Also, is ABC_Temp really a temporary table that's just loaded long enough to get the values into ABC? If not then you are storing duplicate data in multiple places, which is a bad idea. The first problem is that now you need these kinds of update scenarios. There are other issues that you might run into, such as inconsistencies in the data, etc.

另外，ABC_Temp真的是一个临时表，只是加载足够长的时间才能将值输入ABC吗？如果没有，那么你在多个地方存储重复数据，这是一个坏主意。第一个问题是现在您需要这些类型的更新方案。您可能遇到其他问题，例如数据不一致等。

#2

You could use cross apply to seek the last row in ABC_Temp with the same key. Use a where clause to filter out rows with no differences:

您可以使用交叉应用来使用相同的键在ABC_Temp中查找最后一行。使用where子句过滤掉没有差异的行：

update  abc
set     col1 = latest.col1
,       col2 = latest.col2
,       col3 = latest.col3
from    ABC abc
cross apply
        (
        select  top 1 *
        from    ABC_Temp tmp
        where   abc.key = tmp.key
        order by
                tmp.LastUpdatedDate desc
        ) latest
where   abc.col1 <> latest.col1
        or (abc.col2 <> latest.col2
            or (abc.col1 is null and latest.col2 is not null)
            or (abc.col1 is not null and latest.col2 is null))
        or abc.col3 <> latest.col3

In the example, only col2 is nullable. Since null <> 1 is not true, you have to check differences involving null using the is null syntax.

在该示例中，只有col2可以为空。由于null <> 1不成立，因此必须使用is null语法检查涉及null的差异。

#1

UPDATE
    ABC
SET
    price = T1.price,
    qty = T1.qty
FROM
    ABC
INNER JOIN ABC_Temp T1 ON
    T1.item_no = ABC.item_no
LEFT OUTER JOIN ABC_Temp T2 ON
    T2.item_no = T1.item_no AND
    T2.last_updated_date > T1.last_updated_date
WHERE
    T2.item_no IS NULL AND
    (
        T1.price <> ABC.price OR
        T1.qty <> ABC.qty
    )