LIMIT in MySQL Update Row

Question

I am trying to Update Some row in my database. If I run without limit its working fine but if I run it with limit its giving me error like below

1064 - 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 '35' at line 1

My Query is like below

UPDATE number_list SET sync = 0 WHERE server = 1 ORDER by id ASC LIMIT 0,35

Let me know if someone can correct me.

Possible duplicate of [MySQL - UPDATE query with LIMIT](https://stackoverflow.com/questions/6289729/mysql-update-query-with-limit) — Tobias K., Aug 12 '18 at 15:39

score 5 · Accepted Answer · answered Aug 12 '18 at 14:23

5

You can use limit in an update (in MySQL) but not an offset. So just do:

UPDATE number_list
     SET sync = 0
     WHERE server = 1
     ORDER by id ASC
     LIMIT 35;

This is a bit subtle, because SELECT supports offsets. However, it is clear in the syntax diagram for UPDATE.

answered Aug 12 '18 at 14:23

Gordon Linoff

1,242,037
58
646
786

I want set offset in my query like LIMIT 35,35 and LIMIT 70,35 etc. Thanks – Rina Patel Aug 12 '18 at 14:25
2

@RinaPatel . . . That is not the question that you asked. If you have *another* question, then ask another question. As I mentioned, MySQL doesn't support `OFFSET` for the table being updated. – Gordon Linoff Aug 12 '18 at 14:44

LIMIT in MySQL Update Row

1 Answers1