一个表更新

Question

我想更新Price，其中PriceGroup = 'PG1'和Price为null，价格来自相同的ID，但PriceGroup是PG2。我已经尝试了一些解决方案，但无法找到任何与Where语句。

PriceTable:

Fields: ID, Price, PriceGroup
ABC,null,PG1
ABC,1.00,PG2

结果将是:

ABC,1.00,PG1
ABC,1.00,PG2

Answer 1

像这样?

Update priceTable
set price = (select b.price from PriceTable b where priceTable.id = b.id and price is not null )
where price is null

try with this data

INSERT INTO PriceTable VALUES(1, 1, 'PG1');
INSERT INTO PriceTable VALUES(1, null, 'PG2');
INSERT INTO PriceTable VALUES(2, 2,'PG3');
INSERT INTO PriceTable VALUES(2, null,'PG4');

，这是输出

before update
1|1|PG1
1||PG2
2|2|PG3
2||PG4
after update
1|1|PG1
1|1|PG2
2|2|PG3
2|2|PG4