CODEWITHSUNDEEP
sqlsql-server
Quinn
Quinn

Reputation: 607

SQL update value in row with value from another row

I need to iterate through a large data set and populate the Product ID into the Special ID field for the negative BaseCurrencyValue for all Products contained that are contained inside that GUID

So in the example below the Special ID should be 1328 for all rows

I know how to do a straight update like

update mytable
set SpecialID = productID
where SpecialID is null

But I don't know how to update the other non negative Prices into it 

saleitemid productid    quantity    BaseCurrencyPrice   saleguid    specialsetinstanceid specialid
393191236  34           2           10                  48EC5DF9    1                    NULL
393191235  88           2           10                  48EC5DF9    1                    NULL
393191237  1328         1           -5                  48EC5DF9    1                    NULL

Upvotes: 0

Views: 49

Answers (2)

forpas
forpas

Reputation: 164064

With a self join:

update m1
set m1.SpecialID = m2.productID
from mytable m1 inner join mytable m2
on m2.saleguid = m1.saleguid and m2.basecurrencyprice < 0
where m1.SpecialID is null

See the demo.
Results:

> saleitemid | productid | quantity | BaseCurrencyPrice | saleguid | specialsetinstanceid | specialid
> ---------: | --------: | -------: | ----------------: | :------- | -------------------: | :--------
>  393191236 |        34 |        2 |                10 | 48EC5DF9 |                    1 | 1328     
>  393191235 |        88 |        2 |                10 | 48EC5DF9 |                    1 | 1328     
>  393191237 |      1328 |        1 |                -5 | 48EC5DF9 |                    1 | 1328

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can use window functions:

with toupdate as (
      select t.*,
             max(case when BaseCurrencyPrice < 0 then productid end) as new_specialID
      from t
     )
update toupdate
     set specialid = new_specialid
     where new_specialid is not null;

Upvotes: 0

Related Questions