Kamran Ahmed Kamran Ahmed - 1 year ago 79
SQL Question

`UPDATE` and `LIMIT` in `MySQL`

I would like to update a specific range of rows, say starting from

and ending at
. How may I achieve that.

I have tried with:

SET col = 'somevalue'
LIMIT 30, 50

but this doesn't work. Is there any way that I can update these rows?

The error that I get is:

Check the manual ... for the right syntax to use near ' 50'

Answer Source

Your statement is not valid MySQL syntax and it doesn't make sense. The problem with the syntax is that offset is not supported for update statements (see here).

The problem with the logic is that you have no order by clause. MySQL doesn't guarantee the order of tables when processing them. So the "first" twenty rows and the "next" twenty" rows make no difference.

Why doesn't this do what you want?

  SET    col = 'somevalue' 
  LIMIT 20;

If you have a specific column that specifies the ordering, you can use where:

  SET    col = 'somevalue' 
  wHERE ID >= 30 and ID < 50;