You can't specify target table XXX for update in FROM clause (with SUM in subclause)

Question

I'm trying to execute this query:

        UPDATE  
            arc_salon_credit_exposant AS asce
        SET  
            asce.asce_credit_restant = 
                (
                    SELECT 
                        SUM(asce_credit_restant) 
                    FROM
                        arc_salon_credit_exposant
                    WHERE
                        asce_scre_id = '524' AND
                        asce_sexp_id = '719' AND
                        (asce_fam_id is NULL OR
                            asce_fam_id = '168')
                )
        WHERE  
            asce.asce_scre_id = '524' AND
            asce.asce_sexp_id = '719' AND
            asce.asce_fam_id is NULL

but, all I get is a mysql error (#1093 - You can't specify target table 'asce' for update in FROM clause). I've read some questions here at stackoverflow (that's why I tried using an alias), but I can't make it work. I know I have to write the query so Mysql will create a temp table, but.. I can't get this done. Kinda stuck here.

Here's the structure of the table:

Column name Type    Null    Défaut
asce_id int(11) Non 
asce_scre_id    int(11) Non 
asce_sexp_id    int(11) Non 
asce_credit_restant double  Oui NULL
asce_fam_id int(11) Oui NULL

And here's some data:

asce_id asce_scre_id asce_sexp_id asce_credit_restant asce_fam_id

35 524 7885 4900 NULL

17 524 719 200 NULL

45 524 719 100 168

44 524 7885 100 168

Thanks in advance

score 0 · Answer 1 · edited May 23 '17 at 11:56

0

You need to update your table with join like this one

UPDATE  arc_salon_credit_exposant AS asce
JOIN
(     SELECT 
         asce_credit_restant.IDColumn  --<<here your Unique column for self join
         SUM(asce_credit_restant) as tot
      FROM
         arc_salon_credit_exposant
      WHERE
         asce_scre_id = '524' AND
         asce_sexp_id = '719' AND
         asce_fam_id is NULL
) A
ON asce.IDColumn = A.IDColumn
SET asce.asce_credit_restant = A.tot
WHERE  
    asce.asce_scre_id = '524' AND
    asce.asce_sexp_id = '719' AND
    asce.asce_fam_id is NULL

See this example demo

edited May 23 '17 at 11:56

Community

1
1

answered Dec 05 '12 at 10:57

Himanshu

31,810
31
111
133

thanks for your help, @hims056... sadly, I still have Mysql errors :( I can't have this query working - even after doing these changes You suggested. – MiGU Dec 05 '12 at 12:55
@user1453418 - Can you show the data structure and sample data? – Himanshu Dec 05 '12 at 12:56

You can't specify target table XXX for update in FROM clause (with SUM in subclause)

1 Answers1

See this example demo