Mysql delete statement with limit

Question

I'm trying to delete rows from a table but I get an error.

DELETE FROM `chat_messages` ORDER BY `timestamp` DESC LIMIT 20, 50;

I get this error at 50:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 50' at line 1

No idea what's wrong.

Answer 1

⚠ Important: If you have any local changes, they will be lost. With or without --hard option, any local commits that haven't been pushed will be lost.^[*]

If you have any files that are not tracked by Git (e.g. uploaded user content), these files will not be affected.

---

First, run a fetch to update all origin/<branch> refs to latest:

git fetch --all 

Backup your current branch:

git branch backup-master 

Then, you have two options:

git reset --hard origin/master 

OR If you are on some other branch:

git reset --hard origin/<branch_name> 

Explanation:

git fetch downloads the latest from remote without trying to merge or rebase anything.

Then the git reset resets the master branch to what you just fetched. The --hard option changes all the files in your working tree to match the files in origin/master

---

Maintain current local commits

^[*]: It's worth noting that it is possible to maintain current local commits by creating a branch from master before resetting:

git checkout master git branch new-branch-to-save-current-commits git fetch --all git reset --hard origin/master 

After this, all of the old commits will be kept in new-branch-to-save-current-commits.

Uncommitted changes

Uncommitted changes, however (even staged), will be lost. Make sure to stash and commit anything you need. For that you can run the following:

git stash 

And then to reapply these uncommitted changes:

git stash pop 

Answer 2

You cannot specify offset in DELETE's LIMIT clause.

So the only way to do that is to rewrite your query to something like:

DELETE FROM `chat_messages` WHERE id IN (select id from (select id                                            FROM `chat_messages`                                        ORDER BY `timestamp` DESC                                           LIMIT 20, 50) x) 

Supposing that you have primary key id column

UPD: you need to implement double nesting to fool mysql, since it doesn't allow to select from currently modified table (thanks to Martin Smith)