Update statement with nested joins not working

Question

I need to update multiple records in a table based upon the sum of some values in another table. Here is my query:

UPDATE aallinnot2 c SET c.Energ_Kcal = ( SELECT d.id1, SUM( c.Energ_Kcal) 
FROM aaingred a
LEFT JOIN aaweight b ON a.unit = b.uni
LEFT JOIN aallinnot2 c ON a.mfdfsds = c.NDB_No
LEFT JOIN aalinfsds d ON a.fsdsnum = d.id1
WHERE d.own_id =42
GROUP BY id1 ) 
WHERE c.NDB_No
IN (    SELECT DISTINCT  `fsdsnum` 
FROM  `aaingred` 
WHERE  `usernum` LIKE  '42'
)

MySQL said: 

#1093 - You can't specify target table 'c' for update in FROM clause

Unfortunately, I don't know how to get my values without referencing target table 'c'! Is there a workaround for this?

McGarnagle · Answer 1 · 2012-04-26T22:25:55.573

With the crazy table/column names and indecipherable logic, this might be the ugliest query I have ever seen. Congrats. :)

I think the following should work (or this approach). The main problem was untangling the group-by expression-- you need to give the database engine a dataset where each row in the target table is joined to a set that contains the updated value for that row. So here, select the new values in a sub-query, then join that sub-query to the original table.

EDIT Fixed some syntax

    UPDATE 
    (
    SELECT d.id1, SUM (c.Energ_Kcal) AS Sum_Energ_Kcal
        FROM aaingred a
            LEFT JOIN aaweight b ON a.unit = b.uni
            LEFT JOIN aallinnot2 c ON a.mfdfsds = c.NDB_No
            LEFT JOIN aalinfsds d ON a.fsdsnum = d.id1
        WHERE d.own_id =42
        GROUP BY id1
    ) d
    ,aaingred a, aallinnot2 d
    SET Energ_Kcal = d.Sum_Energ_Kcal
    WHERE d.id1 = a.fsdsnum
    AND a.mfdfsds = aallinnot2.NDB_No
    AND c.NDB_No IN (
        SELECT DISTINCT  `fsdsnum` 
        FROM  `aaingred` 
        WHERE  `usernum` LIKE  '42'
    );

#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 (SELECT d.id1, SUM(c.Energ_Kcal) AS Sum_Energ_Kcal FROM aaingred a' at line 1 — Nat, Apr 26 '12 at 21:52
Took another crack at it ... I think that's the correct syntax now. — McGarnagle, Apr 26 '12 at 22:28

Jonathan Sayce · Answer 2 · 2012-04-26T18:22:16.993

0

I'm not sure about mysql, but with SQL Server the statement would be something like this:

    UPDATE aallinnot2 
    SET Energ_Kcal = ( 
    SELECT SUM( c.Energ_Kcal) 
    FROM aaingred a
    LEFT JOIN aaweight b ON a.unit = b.uni
    LEFT JOIN aallinnot2 c ON a.mfdfsds = c.NDB_No
    LEFT JOIN aalinfsds d ON a.fsdsnum = d.id1
    WHERE d.own_id =42) 
    WHERE c.NDB_No
    IN (    SELECT DISTINCT  `fsdsnum` 
    FROM  `aaingred` 
    WHERE  `usernum` LIKE  '42')

You can't alias the table to be updated in the UPDATE clause, but you can in the FROM clause.

edited Apr 26 '12 at 18:22

answered Apr 26 '12 at 18:12

Jonathan Sayce

9,359
5
37
51

I am not sure about SQL server, but a subquery to update a field should return one row and one column. – newtover Apr 26 '12 at 18:18
still get the same error. "#1093 - You can't specify target table 'aallinnot2' for update in FROM clause " – Nat Apr 26 '12 at 18:21
Good spot newtover. I've edited accordingly. Nathaniel - think I'm out of my depth here - I'm pretty sure SQL Server would work with this. – Jonathan Sayce Apr 26 '12 at 18:25
Have a look at the answer to this question: http://stackoverflow.com/questions/8333376/you-cant-specify-target-table-table-name-for-update-in-from-clause – Jonathan Sayce Apr 26 '12 at 18:27
thanks lordsauce, i'll study up on recursive queries. concerning @newtover comment, i am trying to update multiple rows in the Energ_Kcal column with NDB_No equal to the results from the subquery in the IN statement. How should I go about returning multiple disparate rows in my subquery? – Nat Apr 26 '12 at 18:49

Update statement with nested joins not working

2 Answers2