MySQL - UPDATE query with LIMIT

mysql limit sql-update
Rahul Shelke picture Rahul Shelke · Jun 9, 2011 · Viewed 213.1k times · Source

I want to update rows in my table with starting from 1001 to next 1000.

I tried with following query:

UPDATE `oltp_db`.`users` SET p_id = 3 LIMIT 1001, 1000
  1. This is giving me syntax error. Is this correct? am I doing any mistake here.
  2. Can we limit update in this way?

Also, the rows that I am trying to update are having Null value for the column p_id which is having data type INTEGER. Due to this I am not even able to update using following query:

UPDATE `oltp_db`.`users` SET p_id = 3 WHERE p_id = null
  1. Is my above query correct?
  2. What can be done to achieve this?

Answer

Roopchand picture Roopchand · Sep 27, 2012

If you want to update multiple rows using limit in MySQL you can use this construct:

UPDATE table_name SET name='test'
WHERE id IN (
    SELECT id FROM (
        SELECT id FROM table_name 
        ORDER BY id ASC  
        LIMIT 0, 10
    ) tmp
)

Related questions

Maximum number of records in a MySQL database table

What is the upper limit of records for MySQL database table. I'm wondering about autoincrement field. What would happen if I add milions of records? How to handle this kind of situations? Thx!

Read More
Pagination using MySQL LIMIT, OFFSET

I have some code that LIMITs data to display only 4 items per page. The column I'm using has about 20-30 items, so I need to make those spread out across the pages. On the first page, I have: $result = mysqli_…

Read More
MySQL JOIN with LIMIT 1 on joined table

I want to join two tables, but only get 1 record of table2 per record on table1 For example: SELECT c.id, c.title, p.id AS product_id, p.title FROM categories AS c JOIN products AS p ON c.…

Read More