SQL - Updating a table, such that a column is summed given another column's key

Given a table:

| id | price | item | total |
| 0  |  1.0  |  A   |       |
| 1  |  1.0  |  A   |       |
| 2  |  0.1  |  B   |       |
| 3  |  1.0  |  B   |       |
| 4  |  2.1  |  B   |       |
| 5  |  1.0  |  A   |       |
| 6  |  2.0  |  C   |       |

is there an SQL statement that will lead to this ?.

| id | price | item | total |
| 0  |  1.0  |  A   |  3.0  |
| 1  |  1.0  |  A   |  3.0  |
| 2  |  0.1  |  B   |  3.1  |
| 3  |  1.0  |  B   |  3.1  |
| 4  |  2.1  |  B   |  3.1  |
| 5开发者_运维知识库  |  1.0  |  A   |  3.0  |
| 6  |  2.0  |  C   |  2.0  |

Where, each item is has all the prices sum'd. I can do a SELECT ...

SELECT SUM(price), item FROM table GROUP BY item;

but I can't figure out how to do an UPDATE. p.s. I'm using Postgres.

Thanks

Create an AFTER trigger that does the update of the aggregated rows.

Thanks for the answers, it got me thinking some more. I was able to do what I wanted in the end by using a temporary second table.

Here's what I did.

Create the test data:

CREATE TABLE test (id INT PRIMARY KEY,
                   price DECIMAL,
                   item CHAR(1),
                   total DECIMAL);

INSERT INTO test VALUES( 0, 1.0, 'A', NULL ), 
                       ( 1, 1.0, 'A', NULL ),
                       ( 2, 0.1, 'B', NULL ),
                       ( 3, 1.0, 'B', NULL ),
                       ( 4, 2.1, 'B', NULL ),
                       ( 5, 1.0, 'A', NULL ),
                       ( 6, 2.0, 'C', NULL );

Generate the temporary table:

SELECT SUM(price), item INTO temp_table FROM test GROUP BY item;

Update:

UPDATE test SET total = sum FROM temp_table WHERE temp_table.item=test.item;

Clean Up:

DROP TABLE temp_table;

Which yeilds:

select * FROM test ORDER BY id;

 id | price | item | total 
----+-------+------+-------
  0 |   1.0 | A    |   3.0
  1 |   1.0 | A    |   3.0
  2 |   0.1 | B    |   3.2
  3 |   1.0 | B    |   3.2
  4 |   2.1 | B    |   3.2
  5 |   1.0 | A    |   3.0
  6 |   2.0 | C    |   2.0
(7 rows)

UPDATE table SET total = (SELECT SUM(price) FROM test2 WHERE item = 'A' GROUP BY item) WHERE item = 'A';