CODEWITHSUNDEEP
mysqlsqldatabase
ke20
ke20

Reputation: 665

Update Mysql Query Optimisation

I must create a mysql query with a large number of queries (about 150,000)

For the moment the query is:

UPDATE table SET activated=NULL
WHERE (
        id=XXXX
    OR  id=YYYY 
    OR  id=ZZZZ 
    OR  id=...
    ...
)
AND activated IS NOT NULL

Do you know a best way for to do that please?

Upvotes: 1

Views: 88

Answers (2)

Eric Petroelje
Eric Petroelje

Reputation: 60529

If you're talking about thousands of items, an IN clause probably isn't going to work. In that case you would want to insert the items into a temporary table, then join with it for the update, like so:

UPDATE table tb
JOIN temptable ids ON ids.id = tb.id
SET tb.activated = NULL

Upvotes: 3

AnandPhadke
AnandPhadke

Reputation: 13506

UPDATE table 
SET activated = NULL
WHERE id in ('XXXX', 'YYYY', 'zzzz')
AND activated IS NOT NULL

Upvotes: 1

Related Questions