CODEWITHSUNDEEP
mysql
helion3
helion3

Reputation: 37511

Limit deleted records when DELETEing w/Using

I'm normalizing a database by moving several columns to their own tables and using the foreign keys instead. I'm trying to update queries in the app to use joins where possible. In queries that delete data, I'd prefer not to lookup key values and just run the delete with the joins. The only method I've found that works, is by adding USING to the query - however this breaks my use of the LIMIT clause.

DELETE FROM prism_data
USING prism_data
INNER JOIN prism_players p ON p.player_id = prism_data.player_id
INNER JOIN prism_actions a ON a.action_id = prism_data.action_id
WHERE (a.action = 'water-flow')
LIMIT 5000; <--- Was ok before the using/joins were added

I'm trying to delete in batches of 5,000. How can I modify the query so the limit is still valid?

Upvotes: 3

Views: 81

Answers (1)

Sylvain Leroux
Sylvain Leroux

Reputation: 52070

Unfortunately, according to the doc, the LIMIT clause is only valid for single table DELETE:

Single-table syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

Multiple-table syntax:

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

Or:

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

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

On the other hand, sub-queries are allowed. Maybe you could refactor your query to the following form?

DELETE FROM prism_data
WHERE .... IS IN (SELECT ....)
LIMIT 5000

Upvotes: 3

Related Questions