Can't use inner-query

Question

I am trying to simple update query with following syntax:

UPDATE cl
SET cl_s="active_"
WHERE id in (
                SELECT distinct 
                    c.id 
                from
                    cl          c,
                    ac          a,
                    cl_ac       ac
                where
                    c.id=ac.cl_id
                    and
                    a.id=ac.ac_id
                    and 
                    c.cl_s="someval"
                    and 
                    a.ac_no !=""
            )
;

The error I get is: Error Code: 1093. You can't specify target table for update in FROM clause

I've read through posts which mention that MySQL is unable to run query like this. I'd want to know a seamless way to restructure such queries to a structure which works. The solution seems to be using joins instead but I can't seem to grasp the concept.

A pseudo code would be helpful, to help with the understanding. Something like

(Main query)
where var in (subquery generating var list)

to

(main query) inner join (subquery generating var list) on var ???

You should use explicit joins, also you should use naming standards. What is `cl.cl_s`? — dustytrash, Sep 14 '18 at 16:54
`ac.ac_no`... You'll keep running into issues that are difficult to solve with names like these! — dustytrash, Sep 14 '18 at 16:57
@dustytrash the reason for those silly names is that I didn't want to throw away actual names in a public forum. Barmar, I guess you didn't even read my question. I did mention that I've gone through the examples already. I didn't even request for a query - to understand the concept than just getting the dish served readily. I want to learn the reason for such structure with MySQL. — Sushant Vasishta, Sep 17 '18 at 17:20
@SushantVasishta In that case I don't recommend using your SIN/SSN or other private information as a table/column name! — dustytrash, Sep 17 '18 at 17:22
Like I said, the pseudo names were used for a reason. And no, they weren't SIN/SSN. — Sushant Vasishta, Sep 17 '18 at 17:24

score 1 · Accepted Answer · answered Sep 14 '18 at 16:58

You can use JOIN in UPDATE statements in MySQL

UPDATE cl
JOIN cl_ac ON cl.id = cl_ac.cl_id
JOIN ac ON cl_ac.ac_id = ac.id
SET cl.cl_s = 'active_'
WHERE cl.cl_s = 'someval' AND ac.ac_no != ''

PS: Use single-quotes for string literals and date literals in SQL. Double-quotes may be used for delimited identifiers, depending on the sql_mode.

Can't use inner-query

1 Answers1