martedì 27 novembre 2012

Do we really need UPDATE/DELETE with LIMIT?

Reading Planet PostgreSQL last day I found a strange poll about the need to place a LIMIT statement in an UPDATE/DELETE query.
My answer to such question is simply NO, and I placed also a comment on the blog of the proposing author, but I cannot find it anymore...
In order to better understand why I do not believe this feature is required, let's consider a sample query:

UPDATE accounts 
SET active = 'f' 
LIMIT 100;

The idea is to disable the first 100 accounts. This query is fully supported in MySQL.
What is clear to me is that such a query is defective by design: you are using a LIMIT statement on a wrong design of the data and or of the unique constraints on such data. 
The right way to do it is by using a WHERE statement that filters the data you are going to manipulate. While I see that LIMIT is a quick trick to do the job, it is the wrong one since it allows DBAs to implement data in any almost unstructered way being able to manipulate them later using a kind of indexing.
It is true, however, that such a scenario could be useful when importing and handling legacy data, that could come from other old systems and with wrong constraints, but once the database is fully reimplemented the need for such a feature is almost inutily in my opinion.

Nessun commento: