> -- 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.
