update sql result set-CodePudding

declare @tbl as table
(
    ItemId int,
    SOQty int,
    DIQty int ,
    IssueQty int,
    BalanceQty int,
    CreateDate datetime,
    StockQty int
)

insert into @tbl 
values (2, 5, 5, 1, 4, '2021-12-16 19:28:05.200', 80),
       (1, 10, 10, 0,10, '2021-12-16 19:28:32.200', 10), 
       (1, 15, 10, 10,5, '2021-12-16 19:28:34.200', 10),
       (1, 8, 5, 2, 3, '2021-12-16 19:28:35.200', 10)

There are 2 UPDATE statements both update on given condition

update x 
set x.StockQty = tx.StockQty  
from @tbl x
join 
    (select * 
     from 
         (select 
              *,
              row_number() over (partition by itemid order by CreateDate) as RowNo 
          from @tbl) as t 
     where t.RowNo = 1) as tx on tx.CreateDate = x.CreateDate

update x 
set x.StockQty = 0 
from @tbl x
join 
    (select * 
     from 
         (select 
              *, 
              row_number() over (partition by itemid order by CreateDate) as RowNo 
          from @tbl) as t 
     where t.RowNo != 1) as tx on tx.CreateDate = x.CreateDate

I want above query in single updatable statement based on RowNo condition if RowNo=1 then x.StockQty = tx.StockQty else x.StockQty = 0

update x set x.StockQty = case when tx.RowNo = 1 then   x.StockQty = tx.StockQty  else x.StockQty end  from @tbl x
join 
(select * from 
(
select *,ROW_NUMBER()over(partition by itemid order by CreateDate) as RowNo from @tbl  
)as t where t.RowNo = 1) as tx on tx.CreateDate = x.CreateDate

I want a single update statement and I tried so far

CodePudding user response：

It looks like you are over-complicating it, you can just update the derived table directly

UPDATE t
SET StockQty = 0
FROM (
    SELECT 
      *,
      ROW_NUMBER() OVER (PARTITION BY itemid ORDER BY CreateDate) as RowNo 
    FROM @tbl
) AS t
WHERE t.RowNo > 1;

db<>fiddle

CodePudding user response：

Actually your first update query doing nothing, but as you have wanted I think this query below suited your scenario:

update x   
set x.StockQty = CASE WHEN ISNULL(tx.RowNo,0) = 1 THEN tx.StockQty ELSE 0 END
from @tbl x
left join 
    (select *
     from 
         (select 
              *,
              row_number() over (partition by itemid order by CreateDate) as RowNo 
          from @tbl) as t 
     where t.RowNo = 1) as tx on tx.CreateDate = x.CreateDate 
left join 
    (select *
     from 
         (select 
              *, 
              row_number() over (partition by itemid order by CreateDate) as RowNo 
          from @tbl) as t 
     where t.RowNo != 1) as tx2 on tx2.CreateDate = x.CreateDate

CodePudding user response：

You can use a CTE and update the CTE:

   With rankedResults
     As (
 Select *
      , rn = row_number() over (partition by t.itemid order by t.CreateDate)
   From @tbl                    t
        )
 Update rankedResults
    Set StockQty = 0
  Where rn > 1;