MySQL update after N rows

Question

I need to update a table except its top 1000 records. My request is as follows:

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

I know that subquery approcah will work here, but I have a huge table in which 200,000 records satisfy the WHERE and it grows. Therefore, I think that if I follow the subprocess approach, it will not scale as the database grows.

I also saw the LIMIT in UPDATE , but it is up to a certain limit. In my case, this happens after a certain offset and should update all records.

You can also find the total counter and specify it using LIMIT. But the COUNT() request does not work.

Answer 1

You can use a custom 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 need to somehow determine what the first N lines are. User-defined variables give you more options on how to do this. And if you cannot do this efficiently in some select queries, you need to think about how to redo such a table. Perhaps a different approach to indexing, splitting a table, caching some values, etc.

Answer 2

I'm not sure if this will be the right solution, but if you have a unique ID column in your table; let's say ID , for example, you can easily put a predicate by saying WHERE ID > 1000 . Which will only consider rows from the 1001st position, such as

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