CODEWITHSUNDEEP
mysqlsql-updatesql-limit
Bubba
Bubba

Reputation: 180

How can I LIMIT the number of rows that are UPDATED in MySQL?

This UPDATE query crashed my website, I'm not sure why.

There are 39,000 rows to update, and the site crashed after 3,500 rows were updated.

Is there a way to LIMIT the UPDATE to executing say 2,500 UPDATES at a time. Or perhaps a more efficient way of doing it?

UPDATE database_2.user 
SET 
    usergroupid = 15
WHERE userid NOT IN 
(
    SELECT vBulletinId FROM database_1.Users
);

Thanks

Upvotes: 0

Views: 1070

Answers (2)

GMB
GMB

Reputation: 222582

A few tens of thousand rows is a really small volume for a relational database, and you should not face performance issues with this code, unless your hardware is severely undersized.

I would still recommend rewriting the query with not exists:

update database_2.user u2
set u2.usergroupid = 15
where not exists (select 1 from database_1.users u1 where u1.userid = u2.vBulletinId)

This is more efficient, and properly hande possible null values in vBulletinId. For performance, you want an index on database_1.users(vBulletinId).

Upvotes: 1

divanov42
divanov42

Reputation: 99

Use LIMIT operator in your select query.

Upvotes: 0

Related Questions