How to update 50k rows of a huge table?

Question

I have a huge table with over 10M rows. I need to update the following rows:

select *
from customers_card_psp
where status="not_added"
  and psp_id = 2
order by id
limit 50000

Since I have a composite index on customers_card_psp(psp_id, status), query above executes too fast (less than 1 sec). But when I use it in an UPDATE query as a sub-query like this:

update customers_card_psp
set status = "failure"
where id in (select id
             from customers_card_psp
             where status="not_added"
               and psp_id = 2
             order by id
             limit 50000)

it throws:

#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

How can I fix the issue?

Answer 1

you can join the table

update customers_card_psp ccp
JOIN (select id
             from customers_card_psp
             where status="not_added"
               and psp_id = 2
             order by id
             limit 50000) cc
ON ccp.id = cc.id
set status = "failure"