skip to Main Content

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?

2

Answers


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