One Table Update-CodePudding

I want to update Price where PriceGroup = 'PG1' and Price is null, with the Price from the same ID but the PriceGroup is PG2. I have tried a few solution here but couldn't find any with Where statement.

PriceTable:

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

Result will be:

ABC,1.00,PG1
ABC,1.00,PG2

CodePudding user response：

Something like this?

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

Tried 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');

and this is the output

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