Reputation: 25
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
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