Update Lowest RN

Johnathan Simpson 586 Reputation points
2021-08-02T18:30:25.007+00:00

How can I update ONLY the lowest value of rn in my sample DDL below?

Declare @@policedata Table
(
    ordernumber varchar(100)
    ,sc varchar(50)
    ,rn int
)

Insert Into @@PoliceData Values
('abc123', NULL, 1), ('abc123', NULL, 2), ('abc123', NULL, 3)
,('lmn123', NULL, 1), ('lmn123', NULL, 2)

Declare @@supdata Table
(
    ordernumber varchar(100)
    ,sc varchar(50)
    ,sc1 varchar(50)
    ,odate datetime2
)

Insert Into @@supdata Values
('abc123', '10', '0', '2020-01-20 10:31:58.9370000')
,('lmn123', '0', '40', '2020-01-20 10:31:58.9370000')

UPDATE pd
SET pd.sc = sd.sscc
FROM @@policedata
JOIN (
        Select
        ordernumber
        ,SUM(Coalesce(case when CAST(sc As Decimal(16,4)) = 0 THEN Cast(sc1 As Decimal(16,4)) End, Cast(sc1 As Decimal(16,4))) As sscc
        FROM @@supdata 
        WHERE CAST(odate As Date) BETWEEN '2020-01-01' AND '2020-01-30'
        GROUP BY ordernumber) sd
ON pd.OrderNumber = sd.ordernumber
--this is where i'm not sure what to write
WHERE MIN(pd.rn)

MS SQL Server 2016

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
{count} votes

Accepted answer
  1. Guoxiong 8,126 Reputation points
    2021-08-02T20:29:00.34+00:00

    Try this:

    UPDATE p
    SET p.sc = COALESCE(NULLIF(s.sc, 0), NULLIF(s.sc1, 0))
    FROM (
        SELECT *, ROW_NUMBER() OVER(PARTITION BY ordernumber ORDER BY rn ASC) AS rn_by_ordernumber
        FROM @@policedata 
    ) AS p
    INNER JOIN @@supdata AS s ON p.ordernumber = s.ordernumber
    WHERE p.rn_by_ordernumber = 1;
    
    SELECT * FROM @@policedata;
    
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 102.3K Reputation points
    2021-08-02T19:36:31.563+00:00

    Maybe this. (I simplified the SUM in the derived table, because it did not compile, and it seemed unnecessarily complex.) I'm assuming that rn is per ordernumber.

    ; WITH CTE_policedata AS (
             SELECT sc, ordernumber, 
                    rowno = row_number() OVER(PARTITION BY ordernumber ORDER BY rn)
             FROM   @@policedata
         )
         UPDATE pd
         SET pd.sc = sd.sscc
         FROM CTE_policedata  pd
         JOIN (
                 Select
                 ordernumber, SUM(Cast(sc1 As Decimal(16,4))) As sscc
                 FROM @@supdata 
                 WHERE CAST(odate As Date) BETWEEN '2020-01-01' AND '2020-01-30'
                 GROUP BY ordernumber) sd
         ON pd.ordernumber = sd.ordernumber
       WHERE pd.rowno = 1
    

    `