Question 169008 in Mysql Question posted in
The official MySQL documentation can be found here.

“How can I update 50k rows of a huge MySQL table?”

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?

Tags:

2

Answers


  1. 0

    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"
    Login or Signup to reply.
  2. 0

    check the version with select (VERSION())

    and if you can, upgrade to a higher version where limit is supported.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search