Was working with MySQL recently. Noticed that UPDATE command of it has special LIMIT clause which is really useful as for me, e.g.
UPDATE `Product_download` SET `version`='9.2.1' WHERE `product_download_id`=367 LIMIT 1;
Do we need LIMIT clause in UPDATE and DELETE statements for PostgreSQL?
Published
Pasha Golub's Blog 
I proposed this functionality before, but for a different reason. The example you provided should probably have an index on product_download_id if speed is becoming an issue with such a statement.
But the scenario I was thinking of was where you want to clear out a huge amount of old data from a massive table, but wanted to do it in batches. Of course this is possible using plenty of other methods, but nothing would be as simple as: DELETE FROM huge_table WHERE data_date < '2012-01-01' LIMIT 1000000;
This could be run every now and then to minimise load on the system. We wouldn't care which records were deleted in particular, just as long as a lot that met the criteria had been deleted. And we wouldn't require the acquisition of exclusive row-level locks for every candidate row.
LikeLike
Well, yes. My example does not reflect the total convenience. Thanks for the great use case!
LikeLike
On MySQL Cluster, which has hard upper limits for the number of uncommitted row changes, this is even essential to be able to perform certain operations …
Whether this is an argument for DELETE…LIMIT or against MySQL Cluster is left as an excercise to the reader … 😉
LikeLike
Well, do you know math ?
Instead of rolling a dice to guess whether your replicates will drop the same rows as your master, you could just use a modulo to delete X% of your rows at once…
DELETE FROM huge_table WHETE data_date < '2012-01-01' AND id % 100 = 0;
— Do that 100 time, with modulo increasing
The MySQL UPDATE/DELETE with LIMIT is one of their best tool to disrupt the query-based replication results.
LikeLike
If you are going to have “limit”, you will need “order by”, otherwise the order will be undefined. Either way, why not simply use a subselect? Like so:
update users set verified = true where id in (select id from users limit 100)
Or:
delete users where id in (select id from users limit 100)
LikeLike
No, we don’t need ORDER BY. See Thom’s use case. Even if it’s necessary we may add it without any problem.
You’re right! There are a lot of ways doing this. But this is syntactic sugar.
LikeLike
For the purpose of deleting relative small batches you don’t need (nor want) ‘ORDER BY’. You effectively want to work in “disk order”. And the example using the subselect is the imperfect workaround, it doesn’t really guarantee it’s done in 1 pass.
LikeLike
Thanks Justin. Just off the top of my head! 🙂
LikeLike
This could be very useful from a DBA point of view of course, as large bulk operations can be split in smaller chunks.
+1 for me.
LikeLike