On Wed, 2009-12-30 at 09:11 -0800, shulkae wrote: > I want to delete all rows exceeding 400 hours (10 days) and I want to > limit deletion of only 100 records at a time. If your table has a primary key or you can contrive one, then the DELETE FROM tbl WHERE pk in (SELECT pk FROM tbl WHERE hours>400) construct sketched by others will work fine. The much harder case is where you have a table that doesn't have a pk. For instance, if you have a table like name | hours -----+------ bob | 400 bob | 20 and you naively do DELETE FROM tbl WHERE name in (SELECT name FROM tbl WHERE hours>400) then you'll delete *all* bob rows, which I suspect is not what you want. In cases without a pk, try something like BEGIN; CREATE TABLE tbl2 AS SELECT * FROM tbl WHERE hours>400; TRUNCATE tbl; INSERT INTO tbl SELECT * FROM tbl2; COMMIT; The effect is to select the rows you want and replace the contents of tbl. The above will work if you have indexes or views that depend on tbl, but there are simpler variations on this theme if that is not a concern. And for a completely different approach, consider creating a view: CREATE VIEW tbl_le_400 AS SELECT * FROM tbl WHERE hours<=400; Then use tbl_le_400 in lieu of tbl for your selects. -Reece -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
