CODEWITHSUNDEEP
mysqlsubquery
Bernhard Kraus
Bernhard Kraus

Reputation: 329

MySQL can’t specify target table for update in FROM clause

I know there are similiar posts here but I didn't find the appropriate to my problem yet.

In an MySQL table oxarticles I have articles imported from an external program. The program sends each article twice as parent article and as variant with some columns containing additional data.

I want to normalize the table, writing the additional column data into the parent article.

My update query looks as follows:

UPDATE  oxarticles
    SET     oxean = (SELECT oxean FROM oxarticles WHERE oxparentid = oxid),
            oxstock = (SELECT oxstock FROM oxarticles WHERE oxparentid = oxid),
            oxinsert = (SELECT oxinsert FROM oxarticles WHERE oxparentid = oxid)

while the parents oxid is bound to the column oxparentid of the child/variant.

Unfortunately I receive the error:

Your can’t specify target table for update in FROM clause

How can I solve this please?

Upvotes: 0

Views: 258

Answers (2)

Bernhard Kraus
Bernhard Kraus

Reputation: 329

Solved it. Here is my solution.

UPDATE  oxarticles a
    SET     a.oxean = ( SELECT oxean FROM ( SELECT oxean, oxparentid, oxartnum FROM oxarticles) tmp WHERE tmp.oxparentid = a.oxid AND tmp.oxartnum LIKE '%GrLos%' LIMIT 0,1),
            a.oxstock = ( SELECT oxstock FROM ( SELECT oxstock, oxparentid, oxartnum FROM oxarticles) tmp WHERE tmp.oxparentid = a.oxid AND tmp.oxartnum LIKE '%GrLos%' LIMIT 0,1),
            a.oxinsert = ( SELECT oxinsert FROM ( SELECT oxinsert, oxparentid, oxartnum FROM oxarticles) tmp WHERE tmp.oxparentid = a.oxid AND tmp.oxartnum LIKE '%GrLos%' LIMIT 0,1),
            a.oxsubclass = ( SELECT oxsubclass FROM ( SELECT oxsubclass, oxparentid, oxartnum FROM oxarticles) tmp WHERE tmp.oxparentid = a.oxid AND tmp.oxartnum LIKE '%GrLos%' LIMIT 0,1),
            a.oxsort = ( SELECT oxsort FROM ( SELECT oxsort, oxparentid, oxartnum FROM oxarticles) tmp WHERE tmp.oxparentid = a.oxid AND tmp.oxartnum LIKE '%GrLos%' LIMIT 0,1),
            a.oxvarname = '',
            a.oxvarstock = 0,
            a.oxvarcount = 0,
            a.oxvarminprice = 0.00,
            a.oxvarmaxprice = 0.00

Just fyi:

 AND tmp.oxartnum LIKE '%GrLos%'

just identifies a certain data type within the tables data. In my case it is possible that there are articles with real variants. These must not be touched by the update process so I only select articles containing the string "GrLos" in column "oxartnum".

Since MySQL doesn't support udate operations within the same table yet you have to force MySQL to create a temporary table. That's what we have to do with the subselects in the set operation:

SET a.oxean = ( SELECT oxean FROM ( SELECT oxean, oxparentid, oxartnum FROM oxarticles) tmp WHERE tmp.oxparentid = a.oxid AND tmp.oxartnum LIKE '%GrLos%' LIMIT 0,1)

In case there are more than one variantes to an article I limit the subselect result to 1

limit 0,1

Perhaps this may help other people in the future.

Upvotes: 0

Arion
Arion

Reputation: 31239

You could do this:

UPDATE oxarticles a 
    LEFT JOIN oxarticles b ON a.oxid=b.oxparentid
SET a.oxean =b.oxean,
    a.oxstock  =b.oxstock ,
    a.oxinsert  =b.oxinsert

Upvotes: 1

Related Questions