Re: allow LIMIT in UPDATE and DELETE

On 5/19/06, Csaba Nagy <nagy(at)ecircle-ag(dot)com> wrote:
> Hi all,
>
> Currently the LIMIT clause is not allowed in UPDATE or DELETE
> statements. I wonder how easy it would be to allow it, and what people
> think about it ? For our application it would help a lot when processing
> things chunk-wise to avoid long running queries.
>
> The fact that the actual rows processed would be unpredictable does not
> make it less useful for us. We actually don't care which rows are
> processed, we process them all anyway, we just want to make sure it is a
> limited number at a time. A lot of our processes do take large amounts
> of time (hours up to days), and we cannot allow that to be in one
> transaction, the system does on-line processing too...
>
> I guess the low-level infrastructure is already there (from what I
> understood from earlier postings, but I may be wrong), and the question
> is more if this feature is worth to be included or not... and the syntax
> must be adjusted of course if yes.

-- sample data
CREATE TEMP TABLE tab (id serial primary key, n int, t text);
INSERT INTO tab(n) SELECT * FROM generate_series(1,1000);
-- say, you want such an update:
UPDATE tab SET t = 'aqq' WHERE n > 10;
-- but with limit:
UPDATE tab SET t = 'aqq' WHERE id in (SELECT id FROM tab WHERE n > 10
LIMIT 100);
-- or this way (join):
UPDATE tab SET t = 'aqq' FROM (SELECT id FROM tab WHERE n > 10 LIMIT
100) AS tab_ids WHERE tab.id = tab_ids.id;

...this of course assumes that you have a primary key you can use
to "target" the update.

Then again, there are places where there is no primary key, like:

CREATE TABLE foo (t text);
INSERT INTO foo VALUES('aaa');
INSERT INTO foo VALUES('aaa');
...and you want to update first 'aaa' to 'bbb'.

But you can handle it this way:

CREATE TEMP SEQUENCE aaa_temp_seq;
UPDATE foo SET t='bbb' WHERE t='aaa' AND nextval('aaa_temp_seq') <= 1;
-- LIMIT 1
...this of course will suck for big queries (all matching rows will be
searched, but not updated);

Reagrds,
Dawid