sql - SQL update value in row with value from another row

Question

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

标签： sqlsql-server

Answer 1

自加入：

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 

请参阅演示。
结果：

> 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