`UPDATE ... SET ... LIMIT` in MySQL

Question

I would like to update a specific range of rows, say starting from 30 and ending at 50. How may I achieve that.

I have tried with:

UPDATE tab 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'

limit and offset don't make sense without an order by because rows in a relational database are not sorted — user330315
– user330315, Commented Apr 20, 2014 at 13:55

Gordon Linoff · Accepted Answer · 2014-04-20 13:55:54Z

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?

UPDATE tab SET col = 'somevalue' LIMIT 20;

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

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

Community · Accepted Answer · 2017-05-23 12:31:17Z

I think with update you can not use limit as it is in select(offset support), you will have to try like this:-

UPDATE tab SET col = 'somevalue' where id between 30 and 50;

LIMIT can be used with UPDATE but with the row count only, such as

UPDATE tab SET col = 'somevalue' where id > 30 limit 20;

Collectives™ on Stack Overflow

`UPDATE ... SET ... LIMIT` in MySQL

2 Answers 2

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Linked

Related