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

What is the error it is throwing??

Saurabh
– Saurabh

2014-04-20 14:46:32 +00:00
Commented Apr 20, 2014 at 14:46 — Saurabh
– Saurabh, Commented Apr 20, 2014 at 14:46

sebastian_oe · Accepted Answer · 2014-04-20 14:48:10Z

2

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.

answered Apr 20, 2014 at 14:48

sebastian_oe

7,3802 gold badges20 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mr X Over a year ago

can we do it through looping and using TOP

sebastian_oe Over a year ago

What do you mean by looping? And as far as I know, MySQL doesn't support the TOP keyword.

Mr X Over a year ago

Thanks .. I really don't knew it about TOP in #mysql ... Loop if can be done using SP

Collectives™ on Stack Overflow

Multiple Table delete using order by and Limit in MySQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related