CODEWITHSUNDEEP
mysqlsql
user1260928
user1260928

Reputation: 3429

SQL : update table from another grouped table

I have two tables :

Product (P1) 

id,   
amount

Price (P2) 

id  
productId  
calibre  
amount

I want to update P1 amount column so that it is equal to the sum of P2 amount column per productId.

For example, from

P1 

'apple',0  
'orange,0

P2 

'apple', 'A', 5  
'apple', 'B', 6  
'orange' 'AA',1  
'orange' 'BBB', 3  
'orange', 'CCC', 4

After update, P1 is like : 

'apple', 11  
'orange', 8

I can't find the SQL syntax to achieve this.

If someone knows...

Thank you.

Upvotes: 2

Views: 41

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

The most efficient way to do your UPDATE query is to do an INNER JOIN of P1 with a temporary table containing the sums of P2:

MySQL:

UPDATE P1
INNER JOIN
(
    SELECT productId, SUM(amount) AS amount
    FROM P2
    GROUP BY productId
) t
    ON P1.id = t.productId
SET P1.amount = t.amount

SQL Server:

UPDATE P1
SET P1.amount = t.amount
FROM P1 INNER JOIN
(
    SELECT productId, SUM(amount) AS amount
    FROM P2
    GROUP BY productId
) t
    ON P1.id = t.productId

I'm hoping that your database is either MySQL or SQL Server.

Upvotes: 1

sagi
sagi

Reputation: 40481

Since you didn't tag your DBMS, I think this will be correct for most DBMS's :

UPDATE P1
SET p1.amount = (SELECT sum(p2.amount) FROM P2
                 WHERE p1.productID = p2.productID)

If p1 can have products that won't appear in p2, the you should use nvl functions - every dbms can have different functions to deal with nulls - coalesce,nvl ETC... Then change the sum(p2.amount) to:

COALESCE(sum(p2.amount),0)
NVL(sum(p2.amount),0)

Chose which one that suits you

Upvotes: 1

Related Questions