CODEWITHSUNDEEP
sqlt-sql
Jakub Jurkowski
Jakub Jurkowski

Reputation: 25

Conditionaly copy value from one row to another

I would like write select which copy value from one row to another row with condition - and this condition would by TRANSACTIONTYPE. Each row of each order id should have value from TRANSACTIONTYPE=1

My Example table

ID TRANSACTIONTYPE ORDERID VALUE
1   1              XXX     100
2   2              XXX     120
3   4              XXX     140
4   2              XXX     120
5   1              YYY     500
6   2              YYY     400

What i expect in select statment:

ID TRANSACTIONTYPE ORDERID VALUE
1   1              XXX     100
2   2              XXX     100
3   4              XXX     100
4   2              XXX     100
5   1              YYY     500
6   2              YYY     500

I have tried write same select statment for this but I failed

Upvotes: 1

Views: 82

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

You can use window functions:

select t.*,
       max(case when transactiontype = 1 then value end) over (partition by orderid) as new_value
from t;

If you are thinking about updating the value, then I would challenge the data model. In that case, the value should probably be stored in the orders table, where orderid is defined.

Upvotes: 1

Related Questions