Mysql update two columns from a sum of another table grouped by id

Question

What I need is a sql for the following I have tables

P which has columns PID, X, DX (PID is PK)
E which as columns EID, PID, X (EID is PK)

Table E has multiple entries for each PID

The statement which DOES NOT work was

update P,E set P.X=P.X+E.X, P.DX = E.X where P.PID = E.PID

I think it should be an update P set P.X = ?, P.DX = ? select( Sum(X) from E group by PID)

but I don't know how to take the output of the select and join it with P and set two values on each row;

Any help would be appreciated!

Answer 1

You need a join in your update query, mysql supports a multi-table UPDATE as Bill karvin suggested mysql-syntax-for-join-update

UPDATE P  JOIN E ON (P.PID = E.PID)
SET P.X=P.X+E.X, P.DX = E.X 

Answer 2

Try this

update P set P.X= (select sum(x) from E where e.pid=p.pid)

Answer 3

@dianuj's answer is right syntax.

But you have one to many relationship. so, one PID has 2+ values, It is not guarranteed which value is updated.

If you dot not concern this. @dianuj's query is OK. but, Just one value is allowed for UPDATE. this question might help you. UPDATE Syntax with ORDER BY, LIMIT and Multiple Tables