如何利用给定的CROSS APPLY语句实现SQL数据表更新？

我知道可以在UPDATE的FROM子句后添加SELECT语句，但当前查询中的CROSS APPLY内部包含SELECT子句，请问该如何编写对应的UPDATE语句来更新数据表？

相关查询代码如下：

UPDATE ItemDim_DEV
set ActiveFlag = 0,
    EndUTCDate = GETUTCDATE() 
from (select i.SKU, i.itemname, i.category, i.CategoryInternalId, i.itemtype, i.IsActive, i.assetaccount, i.InternalId,
    ni.sku,
    ni.itemname,
    ni.class_name,
    ni.productclass,
    ni.ItemType,
    ni.isactive,
    ni.assetaccount,
    i.CalculatedHash, ni.hashid
    from (
        SELECT i.*
        FROM itemDim_Dev i
        WHERE i.SourceSystem = 'NetSuite'
        --and InternalId = '1692'
    ) i
    CROSS APPLY (
        SELECT (CONVERT([binary](64),hashbytes('SHA2_512',
        concat (ISNULL(im.TargetSKU, ni.sku), ni.itemname, pc.class_name, ni.productclass, ni.ItemType,
        CAST(CASE WHEN ni.IsInactive = 'T' THEN 0 ELSE 1 END AS BIT ), ni.assetaccount)))) hashid,
    ISNULL(im.TargetSKU, ni.sku) sku,
    ni.itemname,
    pc.class_name,
    ni.productclass,
    ni.ItemType,
    CAST(CASE WHEN ni.IsInactive = 'T' THEN 0 ELSE 1 END AS BIT ) isactive, ni.assetaccount
        FROM     NetSuiteInventory ni
        INNER JOIN  Product_Class pc ON ni.productclass = pc.class_id
        LEFT JOIN ItemMapping im ON ni.sku = im.SourceSKU
        WHERE ni.ItemInternalId = CAST(i.InternalId as bigint)
        and
        (CONVERT([binary](64),hashbytes('SHA2_512',
        concat (ISNULL(im.TargetSKU, ni.sku), ni.itemname, pc.class_name, ni.productclass, ni.ItemType,
        CAST(CASE WHEN ni.IsInactive = 'T' THEN 0 ELSE 1 END AS BIT ), ni.assetaccount)))) = i.CalculatedHash
    ) ni

你可以直接把CROSS APPLY整合到UPDATE的FROM子句中，无需嵌套多层子查询，这样既简洁又能保证关联逻辑正确，避免不必要的字段投影。以下是修正后的UPDATE语句：

UPDATE id
SET ActiveFlag = 0,
    EndUTCDate = GETUTCDATE()
FROM ItemDim_DEV id
CROSS APPLY (
    SELECT 
        CONVERT([binary](64),HASHBYTES('SHA2_512',
        CONCAT(ISNULL(im.TargetSKU, ni.sku), ni.itemname, pc.class_name, ni.productclass, ni.ItemType,
        CAST(CASE WHEN ni.IsInactive = 'T' THEN 0 ELSE 1 END AS BIT), ni.assetaccount))) AS hashid
    FROM NetSuiteInventory ni
    INNER JOIN Product_Class pc ON ni.productclass = pc.class_id
    LEFT JOIN ItemMapping im ON ni.sku = im.SourceSKU
    WHERE ni.ItemInternalId = CAST(id.InternalId AS BIGINT)
      AND CONVERT([binary](64),HASHBYTES('SHA2_512',
        CONCAT(ISNULL(im.TargetSKU, ni.sku), ni.itemname, pc.class_name, ni.productclass, ni.ItemType,
        CAST(CASE WHEN ni.IsInactive = 'T' THEN 0 ELSE 1 END AS BIT), ni.assetaccount))) = id.CalculatedHash
) ni
WHERE id.SourceSystem = 'NetSuite'

关键说明 #

• 直接给要更新的ItemDim_DEV表起别名id，在FROM子句后直接关联CROSS APPLY，逻辑更清晰
• 保留原有的CROSS APPLY筛选逻辑，确保只更新那些在NetSuiteInventory中存在匹配哈希值的记录
• 将原嵌套子查询中的SourceSystem = 'NetSuite'条件移到最外层WHERE子句，执行效率更高
• 不需要投影多余字段，CROSS APPLY只需要保证关联条件成立即可，无需返回无关字段

内容的提问来源于stack exchange，提问作者CreandoMentes