Missing limit in mysql update queries

[oscarotero]

I'm getting this error:

Error: Call to undefined method Latitude\QueryBuilder\Query\UpdateQuery::limit()

and have noticed that limit capability is missing in Update queries.
orderBy is missing too.

[shadowhand]

This was actually an intentional decision, because I couldn't think of a single use case for having an update statement with LIMIT. Do you have a real world example?

[oscarotero]

I use it for performance reasons, when the amount of rows to be updated are known. For example:

UPDATE users SET disabled = 1 WHERE id IN (2, 3, 4) LIMIT 3;

[shadowhand]

Does that actually change performance? I assume that id is a primary key, so I don't think anything would be gained by doing that. Have you run EXPLAIN on it to confirm a better strategy is used?

[oscarotero]

Yes, that does not affect to performance. I don't know why, I always used limit in these queries. 😄

Anyway, I guess that this library is focused only in build sql queries using the correct syntax for each database engine. So, if a engine allows to use these clauses, why have these limits?

For example, there's no way to update a row directly based in other clauses than indexes. For example, to update the 5 users with more likes:

UPDATE users SET trending = 1 ORDER BY likes DESC LIMIT 5;

Without order and limit, it forces to select the users first, get their ids and then execute the update.

There's no orderBy in the delete query, so it makes hard to delete, for example, the 100 oldest elements in a table:

DELETE items ORDER BY lastActivity LIMIT 100;

[shadowhand]

The standard SQL way to do this is with a sub-select:

UPDATE users SET trending = 1 WHERE id IN (
    SELECT id FROM users ORDER BY likes DESC LIMIT 5
)

Latitude supports this syntax using field('id')->in($select).

[shadowhand]

I'd be happy to take a PR to add LIMIT, etc to MySQL update/delete queries. It just holds no interest for me because I don't use MySQL and I don't think there are many good use cases for it.

[oscarotero]

Ok, I'm not sql expert so I'll try to do not use these clauses for now.
Just curiosity: delete has already the limit capability. Why it can have it and not update queries?

[shadowhand]

For deletes best practice is to always use a limit to prevent coding errors from deleting the entire table.