Question

这个查询在 MySQL 5.1.57 中给了我一个错误, 虽然在 5.1.53 中工作 :


    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;

错误是: 多个 DELETE 中的未知表f

谢谢!

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...

Answer 1

尝试此查询 -

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;

Answer 2

您是否尝试删除所有表格别名? 推荐了一个类似的解决方案 : 这里。因此以下可能有效 :

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;

Answer 3

在删除后使用 schema 解决了同样的错误 :

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;

友情链接