SQL Server UPDATE Record Only if CROSS APPLY Returns Single Value

eqfvzcg8  于 2024-01-05  发布在  其他
关注(0)|答案(2)|浏览(101)

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)
50few1ms

50few1ms1#

You can just group it up and check if COUNT(*) = 1

UPDATE p
  SET p.fID = sq.id
  FROM processed_inventory AS p
  CROSS APPLY
  (
    SELECT MIN(mp.id) AS 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
          mp.Arg = 'Coil'
        AND
          p.WIDTH = CAST(mp.Width AS Decimal(20,4))
    HAVING COUNT(*) = 1
  ) AS sq
  WHERE 
    p.fID = 0 AND
    (p.Arg IN ('', 'CL') OR p.Arg IS NULL);
  • Do yourself a favour and only quote columns which need quoting. It's annoying otherwise.
c3frrgcw

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:

UPDATE p
  SET p.[fID]   = sq.[id]
  FROM [processed_inventory] AS p
  CROSS APPLY
  (
    SELECT mp.[id], COUNT(*) OVER() AS products /*added a count*/
    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) AND
    sq.products = 1 -- Added

相关问题