CODEWITHSUNDEEP
mysqlmysql-error-1093
Simon
Simon

Reputation: 5698

MySQL | You can't specify target table 'a' for update in FROM clause

DELETE FROM table_a WHERE id IN(
    SELECT table_a.id AS id FROM table_a, table_b 
    WHERE table_a.object_id = 1 AND table_a.code = 'code' 
        AND table_a.code = table_b.code 
        AND table_b.id = table_a.b_id 
        AND table_b.table = 'testTable')

This is a (somewhat simplified) query I want MySQL to execute. I read on other pages of stackoverflow that this wasn't supported and that it's solvable by using JOINS. How could this be 'transcribed' to a query using JOINS? I find it hard to do so, because I've never tried creating DELETE queries with more than one table.

Upvotes: 1

Views: 2999

Answers (4)

pkdkk
pkdkk

Reputation: 3961

My Way ... If you could use that!

DELETE FROM table_a WHERE id IN(SELECT id FROM(SELECT id FROM table_a WHERE userid=99 GROUP BY mobile HAVING COUNT(mobile) > 1) as t2)

Upvotes: 0

jka6510
jka6510

Reputation: 826

This is a common MySQL issue, use a temporary table between the select and update/delete:

DELETE FROM table_a WHERE id IN 
   (select id from 
       (SELECT table_a.id AS id FROM table_a, table_b 
        WHERE table_a.object_id = 1 
        AND table_a.code = 'code' 
        AND table_a.code = table_b.code 
        AND table_b.id = table_a.b_id 
        AND table_b.table = 'testTable')
    ) tempTable

Upvotes: 4

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115600

There are two (slightly different) syntaxes for deleting from mutliple tables. Here's the one without USING:

DELETE a
FROM 
      table_a AS a 
  INNER JOIN 
      table_b AS b
    ON  b.code = a.code
    AND b.id   = a.b_id   
WHERE 
      a.object_id = 1 
  AND a.code = 'code' 
  AND b.`table` = 'testTable'   --- Do you actually have a column named "table"?

Upvotes: 2

kitti
kitti

Reputation: 14834

You can't delete from a table and reference the same table in a subquery — just a limitation of MySQL. Something like the following should work:

DELETE FROM table_a 
USING table_a
INNER JOIN table_b
    ON table_a.code = table_b.code
    AND table_b.id = table_a.b_id
    AND table_b.table = 'testTable'
WHERE table_a.object_id = 1 
    AND table_a.code = 'code'

The important part is USING. If you just join the two tables, you'll delete records from both. USING tells MySQL to use these tables for processing, but only delete from the tables in the FROM clause.

http://dev.mysql.com/doc/refman/5.0/en/delete.html

Upvotes: 5

Related Questions