using limit with delete

Chris Smith wrote:

I'm trying to use a limit clause with delete, but it doesn't work at the
moment

It isn't in the SQL standard, and it would have undefined behavior: the
sort order of a result set without ORDER BY is unspecified, so you would
have no way to predict which rows DELETE would remove.

delete from table where x='1' limit 1000;

You could use a subquery to achieve this:

DELETE FROM table WHERE x IN
(SELECT x FROM table ... ORDER BY ... LIMIT ...);

-Neil

I don't care about the order in my particular case, just that I have to
clear the table.

I'll try the subquery and see how I go :)

Thanks!

Neil Conway wrote:

Chris Smith wrote:

I'm trying to use a limit clause with delete, but it doesn't work at
the moment

It isn't in the SQL standard, and it would have undefined behavior: the
sort order of a result set without ORDER BY is unspecified, so you would
have no way to predict which rows DELETE would remove.

delete from table where x='1' limit 1000;

You could use a subquery to achieve this:

DELETE FROM table WHERE x IN
(SELECT x FROM table ... ORDER BY ... LIMIT ...);

-Neil

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
Regards,

Chris Smith

Unit 2, 3 National Street, Rozelle, NSW 2039 Australia

Ph: +61 2 9555 5570
Fx: +61 2 9555 5571

email: info@interspire.com
web: http://www.interspire.com

On Thu, Apr 07, 2005 at 12:02:24PM +1000, Neil Conway wrote:

Chris Smith wrote:

I'm trying to use a limit clause with delete, but it doesn't work at the
moment

It isn't in the SQL standard, and it would have undefined behavior: the
sort order of a result set without ORDER BY is unspecified, so you would
have no way to predict which rows DELETE would remove.

delete from table where x='1' limit 1000;

You could use a subquery to achieve this:

DELETE FROM table WHERE x IN
(SELECT x FROM table ... ORDER BY ... LIMIT ...);

In particular, x can be the system column "ctid", a trick which could be
useful to remove duplicates, for example.

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Porque Kim no hacia nada, pero, eso s�,
con extraordinario �xito" ("Kim", Kipling)

For the archives...

Using 7.4 so IN() is a little slower, so I rewrote it slightly to be

DELETE FROM table WHERE EXISTS (select x from table .... LIMIT ...);

Works very nicely :)

Thanks again.

Neil Conway wrote:

Chris Smith wrote:

I'm trying to use a limit clause with delete, but it doesn't work at
the moment

It isn't in the SQL standard, and it would have undefined behavior: the
sort order of a result set without ORDER BY is unspecified, so you would
have no way to predict which rows DELETE would remove.

delete from table where x='1' limit 1000;

You could use a subquery to achieve this:

DELETE FROM table WHERE x IN
(SELECT x FROM table ... ORDER BY ... LIMIT ...);

-Neil

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
Regards,

Chris Smith

Unit 2, 3 National Street, Rozelle, NSW 2039 Australia

Ph: +61 2 9555 5570
Fx: +61 2 9555 5571

email: info@interspire.com
web: http://www.interspire.com

Hi Chris,

Just a thought: if you have to clear the table anyway, wouldn't it work
for you to use truncate ? That should be faster than delete.

HTH,
Csaba.

Hi Csaba,

Because I have a where clause limiting which records I'm deleting.

I'm deleting old info from a database, so I'm doing:

DELETE FROM sessions WHERE EXISTS (SELECT sessiontime FROM sessions
WHERE sessiontime < (timenow-7days) LIMIT 100)

(timenow-7days is evaluated in PHP and made an int).

So every time the page gets hit, I'm deleting up to 100 records that are
older than 7 days..

Csaba Nagy wrote:

Hi Chris,

Just a thought: if you have to clear the table anyway, wouldn't it work
for you to use truncate ? That should be faster than delete.

HTH,
Csaba.

On Thu, 2005-04-07 at 04:11, Chris Smith wrote:

I don't care about the order in my particular case, just that I have to
clear the table.

I'll try the subquery and see how I go :)

Thanks!

Neil Conway wrote:

Chris Smith wrote:

I'm trying to use a limit clause with delete, but it doesn't work at
the moment

It isn't in the SQL standard, and it would have undefined behavior: the
sort order of a result set without ORDER BY is unspecified, so you would
have no way to predict which rows DELETE would remove.

delete from table where x='1' limit 1000;

You could use a subquery to achieve this:

DELETE FROM table WHERE x IN
(SELECT x FROM table ... ORDER BY ... LIMIT ...);

-Neil

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
Regards,

Chris Smith

Unit 2, 3 National Street, Rozelle, NSW 2039 Australia

Ph: +61 2 9555 5570
Fx: +61 2 9555 5571

email: info@interspire.com
web: http://www.interspire.com

On Thu, Apr 07, 2005 at 11:51:10 +1000,
Chris Smith <chris@interspire.com> wrote:

Is there another way to approach this?

I'm trying to delete records through a webapp and if there are 500,000
records for example, I can't really leave the page open and expect it to
finish...

Maybe you could use a statement timeout and schedule a batch delete if
the delete times out?