How do I construct this SQL query?

[lozina]

I would like to have a query which UPDATEs rows with the results of a query based on a condition.

Example:

Here is table "test_parent":

...test_id...|...result...|
......1........|....NULL...|
......2........|....NULL...|
......3........|....NULL....|

Here is table "test_child": (test_parent to test_child is a one-to-many relationship)

...test_id...|...num1...|...num2...|
......1........|.....2.......|......3.......|
......1........|......1......|......4.......|
......2........|......4......|......3.......|
......3........|......3......|.......3......|
......3........|......2......|......4.......|
......3........|......2......|.......2......|

I want to have one query that would update the column "result" with the result of the calculation (num1 * num2) of the table test_child where the column test_id matches

here's what I thought would work:

UPDATE test_parent SET result =
(SELECT sum(num1 * num2) FROM test_child tc WHERE tc.test_id = test_id)
WHERE test_id IN (1,2,3)

That last WHERE clause with the IN statement is also required because when I run this query on the real system I also need to limit to calc to certain IDs...

My query above is FAIL - it updates each row with the TOTAL calculation of num1*num2 for EVERY row in test_child. So in this case, each result column in test_parent is set to 43, but the desired result is that result for test_id 1 should be 10, for test_id 2 result should be 12 and test_id 3 is 21

Any idea how to get this to work properly?

 

[nickbits]

try this:

UPDATE test_parent SET result =
(SELECT sum(num1 * num2) FROM test_child tc WHERE tc.test_id = test_id GROUP BY tc.test_id)
WHERE test_id IN (1,2,3)

 

[lozina]

Originally posted by: nickbits
try this:

UPDATE test_parent SET result =
(SELECT sum(num1 * num2) FROM test_child tc WHERE tc.test_id = test_id GROUP BY tc.test_id)
WHERE test_id IN (1,2,3)

I get the following error:

#1242 - Subquery returns more than 1 row

PS - I am using MySQL sorry I forgot to mention that!

 

[lozina]

I guess I'll just have to do a loop in php then foreach test_id ??

 

[KLin]

UPDATE test_parent SET result = sub.TestSum
FROM Test_parent,
(SELECT tc.test_id, sum(num1 * num2) As TestSum FROM test_child tc Group By tc.Test_ID) sub
WHERE test_parent.test_id IN (1,2,3) AND test_parent.test_id = sub.test_id

try that?

 

[Kntx]

UPDATE TP SET TP.result = TC.num1 * TC.num2
FROM test_parent TP
JOIN test_child TC ON TP.test_id = TC.test_id;

 

[KLin]

Originally posted by: Kntx
UPDATE TP SET TP.result = TC.num1 * TC.num2
FROM test_parent TP
JOIN test_child TC ON TP.test_id = TC.test_id;

He wants the sum of the product of num1 and num2 for each testID.

 

[lozina]

Originally posted by: KLin
UPDATE test_parent SET result = sub.TestSum
FROM Test_parent,
(SELECT tc.test_id, sum(num1 * num2) As TestSum FROM test_child tc Group By tc.Test_ID) sub
WHERE test_parent.test_id IN (1,2,3) AND test_parent.test_id = sub.test_id

try that?

Dang, that looked very promising

...but I get this error:

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM Test_parent,
(SELECT tc.test_id, sum(num1 * num2) As TestSum FROM test_chil' at line 2
(0 ms taken)

[the error message cuts off some text purposely, the query is exact copy & paste of what your wrote]

 

[lozina]

P.S. Here is SQL script to create the same tables that I am testing this query with:

 

[KLin]

UPDATE test_parent, (SELECT tc.test_id, sum(num1 * num2) As TestSum FROM test_child tc Group By tc.Test_ID) sub
SET test_parent.result = sub.TestSum
WHERE test_parent.test_id IN (1,2,3) AND test_parent.test_id = sub.test_id

try that?

 

[Cogman]

Wait, so you want every field in the table that matches your conditions to have the exact same value (Mainly the row1 num1 * num2 + row2 num1 * num2... ect) or do you want a different value for each row that matches your where condition?

I came up with something like this. You might want a different behavior, IDK.

Update test_parent t1, test_child t2 set t1.result=t2.num1 * t2.num2 where t1.test_id=t2.test_id and t1.test_id in (1,2,3);

 

[KLin]

Originally posted by: Cogman
Wait, so you want every field in the table that matches your conditions to have the exact same value (Mainly the row1 num1 * num2 + row2 num1 * num2... ect) or do you want a different value for each row that matches your where condition?

I came up with something like this. You might want a different behavior, IDK.

Update test_parent t1, test_child t2 set t1.result=t2.num1 * t2.num2 where t1.test_id=t2.test_id and t1.test_id in (1,2,3);

Won't work. Look at his child table. He has multiple records of the same test_id. He wants the sum of the product of num1 and num2 for each test_id.

 

[lozina]

Originally posted by: KLin
UPDATE test_parent, (SELECT tc.test_id, sum(num1 * num2) As TestSum FROM test_child tc Group By tc.Test_ID) sub
SET test_parent.result = sub.TestSum
WHERE test_parent.test_id IN (1,2,3) AND test_parent.test_id = sub.test_id

try that?

BINGO!

We have a winner!

Thanks KLin
:thumbsup::thumbsup:

 

[KLin]

Originally posted by: lozina

Originally posted by: KLin
UPDATE test_parent, (SELECT tc.test_id, sum(num1 * num2) As TestSum FROM test_child tc Group By tc.Test_ID) sub
SET test_parent.result = sub.TestSum
WHERE test_parent.test_id IN (1,2,3) AND test_parent.test_id = sub.test_id

try that?

BINGO!

We have a winner!

Thanks KLin
:thumbsup::thumbsup:

Just a matter of getting the syntax correct for MySQL. Most of my SQL experience is with MSSQL. Glad I could help.