mysql can't specify target table for update in FROM clause

Question

I have to check some unwanted records and remove it for that i have used below query

delete from contain_tab 
where org_id = "RTY" 
      AND id_contain 
           NOT IN (
               select id_contain from(
                  SELECT id_contain FROM contain_tab c 
                  WHERE org_id = "RTY" 
                  AND (c.cu_date,c.cv_date) 
                  in(
                       select max(cu_date),max(cv_date) from contain_tab cs 
                       where org_id = "RTY" 
                       AND cs.container = c.container 
                       and cs.mfest_id = c.mfest_id 
                       and cs.sl_number = c.sl_number)
                     ) 
              as id_contain)

Structure

CREATE TABLE `contain_tab` (
`id_contain` int(11) NOT NULL,
`org_id` varchar(5) NOT NULL,
`container` varchar(24) DEFAULT NULL,
`eta_dest` date DEFAULT NULL,
`mfest_id` varchar(30) DEFAULT NULL,
`sl_number` varchar(30) DEFAULT NULL,
`orig_id` varchar(8) DEFAULT NULL,
`cu_date` date DEFAULT NULL,
`cv_date` date DEFAULT NULL,
`vehicle` varchar(32) DEFAULT NULL,
`user_defined_field1` varchar(35) DEFAULT NULL,
`user_defined_field2` varchar(35) DEFAULT NULL
`created_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I got error MySQL said: Documentation

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

Please help me to fix the error

Please check this http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause — Ninja, Feb 10 '17 at 06:29
Ya modified Actually its working in local machine in server its thrown error — raj, Feb 10 '17 at 06:41

score 0 · Answer 1 · answered Feb 10 '17 at 06:30

0

Give backticks for your tablename containas it is a keyword

`contain`

answered Feb 10 '17 at 06:30

affaz

1,191
9
23

Ya modified Actually its working in local machine in server its thrown error – raj Feb 10 '17 at 06:41
Issue still exist – raj Feb 10 '17 at 06:47
@raj what did you mean by this line `(select id_contain from( SELECT id_contain ` – affaz Feb 10 '17 at 06:48
"(select id_contain from" [first part] without this select its thrown error so only i put that but its working in local machine in server its through error – raj Feb 10 '17 at 06:53
currently theres not contain in your query and what is the error shown now? – affaz Feb 10 '17 at 06:54
MySQL said: Documentation #1093 - You can't specify target table 'contain_tab' for update in FROM clause – raj Feb 10 '17 at 07:11
@raj Could you post your data structure – affaz Feb 10 '17 at 07:26
data structure Posted – raj Feb 13 '17 at 13:56

score 0 · Answer 2 · answered Feb 10 '17 at 09:21

Try using the backticks as your names include reserved keywords. I've taken the liberty of formatting your query.

Have a look at the MySQL reserved keywords and try to avoid them in the names of your database/tables/columns.

delete from `contain_tab`
where `org_id` = "RTY"
      AND `id_contain`
          NOT IN (
            select `id_contain`
            from(
                  SELECT `id_contain`
                  FROM `contain_tab` c
                  WHERE `org_id` = "RTY"
                        AND (c.`cu_date`, c.`cv_date`)
                            in (
                              select max(`cu_date`), max(`cv_date`)
                              from `contain_tab` cs
                              where `org_id` = "RTY"
                                    AND cs.`container` = c.`container`
                                    and cs.`mfest_id` = c.`mfest_id`
                                    and cs.`sl_number` = c.`sl_number`
                            )
                ) as "id_contain"
          );

#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 '"id_contain" )' at line 19 After remove " in id_contain #1093 - You can't specify target table 'contain_tab' for update in FROM clause — raj, Feb 13 '17 at 13:58
@raj try removing double quotes for `id_contain` in last line — affaz, Feb 13 '17 at 14:18

mysql can't specify target table for update in FROM clause

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

2 Answers2