> -- 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. ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend