From: | Csaba Nagy <nagy(at)ecircle-ag(dot)com> |
---|---|
To: | Dawid Kuroczko <qnex42(at)gmail(dot)com> |
Cc: | Postgres general mailing list <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: allow LIMIT in UPDATE and DELETE |
Date: | 2006-05-19 13:21:01 |
Message-ID: | 1148044860.17461.371.camel@coppola.muc.ecircle.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> -- 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
Like I said in a previous post, I can figure out the workarounds, but it
would be nice not to need it, not to mention the LIMIT would work
faster.
I specifically seek for opinions about the LIMIT on DELETE/UPDATE
feature, not workarounds.
Cheers,
Csaba.
From | Date | Subject | |
---|---|---|---|
Next Message | Csaba Nagy | 2006-05-19 13:26:37 | Re: allow LIMIT in UPDATE and DELETE |
Previous Message | Csaba Nagy | 2006-05-19 13:17:59 | Re: allow LIMIT in UPDATE and DELETE |