MySQL Update after N rows

Question

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.

Answer 1

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.