CODEWITHSUNDEEP
mysql
Maxime Laval
Maxime Laval

Reputation: 4388

Unknown table in MULTI DELETE

This query gives me an error in MySQL 5.1.57, works in 5.1.53 though:


    DELETE f
    FROM table1 AS f
    JOIN table2 AS dsy
    JOIN table3 AS ds
    JOIN table4 AS dp
    JOIN table5 AS dg
    WHERE
    dsy.f1 = f.f1
    AND ds.f2 = f.f2
    AND dp.f3 = f.f3
    AND dg.f4 = f.f4
    AND dsy.school_year = 2011
    AND ds.id = 29620
    AND dp.id = 14120
    AND dg.grade_level = 5;

The error is: Unknown table 'f' in MULTI DELETE

Thanks!

EDIT: Actually this query works, the thing is I was using the schema name to declare my tables like schema.table1 (I removed it to post a more clear query here), with the schema name it breaks...

Upvotes: 15

Views: 40429

Answers (3)

mr_squall
mr_squall

Reputation: 2381

I resolved same error with using schema after delete:

DELETE **`schemaX`.f**
    FROM `schemaX`.table1 AS f
    JOIN table2 AS dsy
    JOIN table3 AS ds
    JOIN table4 AS dp
    JOIN table5 AS dg
    WHERE
    dsy.f1 = f.f1
    AND ds.f2 = f.f2
    AND dp.f3 = f.f3
    AND dg.f4 = f.f4
    AND dsy.school_year = 2011
    AND ds.id = 29620
    AND dp.id = 14120
    AND dg.grade_level = 5;

Upvotes: 1

Devart
Devart

Reputation: 121922

Try this query -

DELETE f
FROM table1 AS f
JOIN table2 AS dsy
  ON dsy.f1 = f.f1
JOIN table3 AS ds
  ON ds.f2 = f.f2
JOIN table4 AS dp
  ON dp.f3 = f.f3
JOIN table5 AS dg
  ON dg.f4 = f.f4
WHERE
  dsy.school_year = 2011 AND ds.id = 29620 AND dp.id = 14120 AND dg.grade_level = 5;

Upvotes: 24

Korhan Ozturk
Korhan Ozturk

Reputation: 11308

Have you tried removing all of your table alias? There is a similar solution proposed here. So the following might work:

DELETE table1
    FROM table1 
    JOIN table2 
    JOIN table3 
    JOIN table4 
    JOIN table5 
    WHERE
    table2.f1 = table1 .f1
    AND table3.f2 = table1.f2
    AND table4.f3 = table1.f3
    AND table5.f4 = table1.f4
    AND table2.school_year = 2011
    AND table3.id = 29620
    AND table4.id = 14120
    AND table5.grade_level = 5;

Upvotes: 3

Related Questions