CODEWITHSUNDEEP
mysqlsqlperformancesql-updatelimit
Happy Coder
Happy Coder

Reputation: 4692

MySQL Update after N rows

I need to update a table except its top 1000 records. My query is like this :

UPDATE tableA 
SET price = 100 
WHERE price = 200 AND
      item_id =12 AND 
      status NOT IN (1,2,3);

I know the subquery approcah will work here but I am having a huge table in which 200000 records satisfy the WHERE condition and it is growing. So I think if I follow the sub query approach, it will will not scale as the DB grows.

Also I have seen specifying LIMIT in UPDATE query but it is up to a certain limit. In my case it is after a certain offset and should update all the records.

Also it is possible to find the total count and specify it with LIMIT. But the COUNT() query is failing.

Upvotes: 7

Views: 2886

Answers (2)

David162795
David162795

Reputation: 1866

You can use user defined variable:

SET @X = (SELECT ID FROM tableA 
    WHERE price = 200 AND item_id =12 AND status NOT IN (1,2,3) 
    ORDER BY ID LIMIT 1000,1 );
UPDATE tableA SET price = 100 
    WHERE price = 200 AND item_id =12 AND status NOT IN (1,2,3) 
    AND ID>@X;

Yes, you will need some way how to define "what is first N rows". User defined variables gives you just more options how to do it. And If you can not do it effectively in some select query, you will need to think about some way how to rework such table. Maybe different indexing approach, splitting the table, caching some values, etc.

Upvotes: 1

Rahul
Rahul

Reputation: 77896

I am not sure if this would be a right solution but if you have a unique ID column in your table; lets say ID for example then you can place the predicate very easily saying WHERE ID > 1000. Which will consider only rows from 1001th position like 

UPDATE tableA SET price = 100 
WHERE price = 200 
AND item_id = 12
AND ID > 1000

Upvotes: 0

Related Questions