Postgres Pro
Downloads
Home   >   mailing lists

Thread: ADD TO UPDATE LIMIT

ADD TO UPDATE LIMIT

From
Andrey Furs
Date:
Hi there!
Are you serious !?
Postgresql not UPDATE LIMIT !?

UPDATE table SET id=100 WHERE id<1000 LIMIT 1;

OMG!


Re: ADD TO UPDATE LIMIT

From
Christophe Pettus
Date:

> On Oct 23, 2020, at 09:36, Andrey Furs <aefurs93@gmail.com> wrote:
>
> Hi there!
> Are you serious !?
> Postgresql not UPDATE LIMIT !?
>
> UPDATE table SET id=100 WHERE id<1000 LIMIT 1;
>
> OMG!

What does that even mean, though?  There's no guarantee as to which row is going to be updated, even if that syntax
worked. LIMIT without an ORDER BY is not statistically random enough to be useful for true randomness, but is not
definedenough to be safe.  What you probably want is: 

    UPDATE t SET id=100 WHERE id = ( SELECT id FROM t ORDER BY id LIMIT 1);

--
-- Christophe Pettus
   xof@thebuild.com

Re: ADD TO UPDATE LIMIT

From
"David G. Johnston"
Date:
On Fri, Oct 23, 2020 at 10:32 AM Andrey Furs <aefurs93@gmail.com> wrote:
Hi there!
Are you serious !?
Postgresql not UPDATE LIMIT !?

UPDATE table SET id=100 WHERE id<1000 LIMIT 1;

As Christophe said, you failed to describe what you expected to happen.  If you are looking for SQL to protect you from yourself then this is but one example that suggests that you need to change your general expectations and just be more diligent about testing your data destructing SQL commands.  If you actually expected it to change a single record when more than one match you likewise need to change your expectations - though in this case there isn't much to be done when writing SQL generally.

David J.