I need to do an update to the [processed_inventory] table, but only if there is a single mp.[id] returned by the first CROSS APPLY. I included the whole query though most of it probably doesn't matter.
UPDATE p
SET p.[fID] = sq.[id]
FROM [processed_inventory] AS p
CROSS APPLY
(
SELECT mp.[id] FROM [merged_products] AS mp
CROSS APPLY
STRING_SPLIT(mp.[M_Num], ';') AS ss
WHERE
ss.[value] = p.[MNUMBER] AND
(
(p.[WIDTH] < 30 AND mp.[File] = '1') OR
(p.[WIDTH] >= 30 AND mp.[File] = '2')
) AND (
(p.[Arg] = 'CL' AND mp.[Arg] = 'Coil') OR
(p.[Arg] = '' AND mp.[Arg] = 'Coil') OR
(p.[Arg] IS NULL AND mp.[Arg] = 'Coil')
) AND
p.[WIDTH] = CAST(mp.[Width] AS Decimal(20,4))
) AS sq
WHERE
p.fID = 0 AND
(p.[Arg] = 'CL' OR p.[Arg] = '' OR p.[Arg] IS NULL)
2条答案
按热度按时间50few1ms1#
You can just group it up and check if
COUNT(*) = 1
c3frrgcw2#
A bit hard to validate the result without test data but you can do something like this, using window function COUNT to get total number of rows together with respective ids: