Multiple Table delete using order by and Limit in MySQL

Question

When using delete statement for single table with Limit and ORDERBY its working fine

DELETE FROM test_users_table  ORDER BY test_users_table_cname DESC
LIMIT 5

Can we also achieve delete in multiple table using ORDER BY AND LIMIT option ..

Here is what I am using

  DELETE test_users_table, test_user_data_table FROM test_users_table 
    JOIN test_user_data_table 
    ON test_users_table.table1_id = test_user_data_table.table2_userid
    ORDER BY test_users_table.cname DESC
    LIMIT 5

For multiple table its throwing error

Answer 1

According to the MySQL documentation, this is the syntax for deleting from multiple tables:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
tbl_name[.*] [, tbl_name[.*]] ...
FROM table_references
[WHERE where_condition]

Additionally it states:

For the multiple-table syntax, DELETE deletes from each tbl_name the rows that satisfy the conditions. In this case, ORDER BY and LIMIT cannot be used.

Thus, you can delete from multiple tables. However, you cannot use the LIMIT keyword directly in it.

What's the assumed semantic for your query? Should the statement delete the first 5 entries from table A, table B or 5 entries from each table? This information might help to restructure your query.