Insert missing row(s)
I have a table of supply/price curve, where for some items the zero price row is missing, and I want to insert that row for those items. The table looks like this:
Item Point Price Quantity
-----------------------------
A 1 0 0
A 2 100 5
A 3 200 10
B 1 50 6
B 2 70 8
The number of rows per item can be different (like 3 points for item A, and less or more points for another item). In the above, the zero price and quantity point is missing for item B, so the updated table should look like this:
Item Point Price Quantity
-----------------------------
A 1 0 0
A 2 100 5
A 3 200 10
B 1 0 0
B 2 50 6
B 3 70 8
Where a new row (point with zero price/quantity) is added and point of the other rows is updated. The new row should be added for all items where point 1 is not Price=0 and Quantity = 0. How to do it in Oracle sql server?
Hmmm, you can do this in two steps. First, I would insert point with a value of 0 and then increment the value:
insert into t(item, point, price, quantity)
select item, 0 as point, 0 as price, 0 as quantity
from t
group by item
having min(price) <> 0;
Then, increment the point column:
update t
set point = point + 1
where exists (select 1 from t t2 where t2.item = t.item and t2.point = 0);
commit;
上一篇: BQ SQL解决方案，用于根据方差比较行
下一篇: 插入缺少的行（s）