How can I update a table using the following CROSS APPLY?-CodePudding

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

I understand that I can put a select after the from of the update but in the CROSS APPLY that is in the query starts with a select as in the second one, can you help me please.

CodePudding user response：

Make a join to the target table

UPDATE A
SET XXX = YYY
FROM ItemDim_DEV A
JOIN (
   --huge SELECT that I recommend you try to shorten in the next questions
   CROSS APPLY (any secrets...)
) B ON A.PK = B.PK