Re: Deleting 100 rows which meets certain criteria

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