SQL Error (1093): You can not specify target table 'import2016' for update in FROM clause

Question

SQL Error (1093): You can not specify target table 'import2016' for update in FROM clause

Navigation

#1 by (0 votes)

0

I'm running this PROCEDURE, and it returns this error!

BEGIN
        SET @cont = 0;
        REPEAT
            SET @sqlstring = "UPDATE import2016 SET coluna4 = (SELECT coluna4 FROM import2016 AS a WHERE a.coluna2 ='' and a.coluna4 like'%/%' LIMIT ?,01) WHERE id = (SELECT id+7 FROM import2016 AS a WHERE a.coluna2 ='' and a.coluna4 like'%/%' LIMIT ?,01);";
            PREPARE stmt FROM @sqlstring ;
            EXECUTE stmt USING @cont,@cont;
            DEALLOCATE PREPARE stmt;
            SET @cont = @cont +1;
        UNTIL @cont = 256
        END REPEAT;
    END


/* Erro SQL (1093): You can't specify target table 'import2016' for update in FROM clause */

PROCEDURE is on the same bench of the table! I would like to know how I can resolve this error, thank you.

mysql stored-procedures

asked by anonymous 17.01.2018 / 21:11

1 answer

DevExpress XAF Express Framework Framework Using MySQL to create a Select that looks for words "together"

score 0 · Accepted Answer

I was able to solve using the concept that @Rovann Linhalis said:

In the subquery, you put the import2016 table alias as the [import2016 AS a], but the select column was only column4, without the by the way. I just suggested that you put the alias in the sub selects too, because SGDB can understand that that column is the table that is informed in the update. (Even though it's the same table)

and the answer to another question here in stackoverflow:

Update with Select

Once the query was:

UPDATE capaobonito_import.import2016_copy 
SET coluna4 = 
(   
    SELECT b.coluna4 FROM import2016 AS b 
    WHERE b.coluna2 ='' and b.coluna4 like'%/%' LIMIT 00,01
) 
WHERE id =
(
    SELECT a.id+7 FROM import2016_copy_copy AS a 
    WHERE a.coluna2 ='' and a.coluna4 like'%/%' LIMIT 00,01
);

Thanks for the help of the Community, in particular the commentators and answers referenced here.