Delete Question

On 12/7/05, Alex <alex@meerkatsoft.com> wrote:

I have a table where I store changes made to an order. The looks like
ProdID, ChangeDate, Change1, Change2, ... etc.
Some ProdIDs have multiple records.
Is there an easy way to delete all records of a ProdID except the most
recent (ChangeDate is timestamp) one? Preferably in one SQL statement?

delete from table_name where exists (select * from table_name x where
x.prodid = table_name.prodid and x.changedate > table_name.changedate);

this should work.

depesz

On Dec 7, 2005, at 16:21 , Alex wrote:

Is there an easy way to delete all records of a ProdID except the
most recent (ChangeDate is timestamp) one? Preferably in one SQL
statement?

Here's one way to do it, though not it one SQL statement:

create table copy_of_original_table as
select distinct on ("ProdID") "ProdID", ...
from original_table
order by "ChangeDate" desc;

truncate original_table;

insert into original_table ("ProdID", ... )
select "ProdID", ...
from copy_of_original_table;

Note you need to quote the column names if case is important.

Michael Glaesemann
grzm myrealbox com

am 07.12.2005, um 18:21:25 +1100 mailte Alex folgendes:

Hi,

I have a table where I store changes made to an order. The looks like
ProdID, ChangeDate, Change1, Change2, ... etc.
Some ProdIDs have multiple records.

Is there an easy way to delete all records of a ProdID except the most
recent (ChangeDate is timestamp) one? Preferably in one SQL statement?

test=# select * from change ;
id | datum | text
----+----------------------------+----------
1 | 2005-12-07 08:28:28.939312 | foo
1 | 2005-12-07 08:28:34.695091 | foo2
1 | 2005-12-07 08:28:37.150354 | foo3
1 | 2005-12-07 08:28:43.263171 | foo_last
2 | 2005-12-07 08:28:48.419252 | foo
2 | 2005-12-07 08:28:55.819969 | foo_last
(6 rows)

test=# begin;
BEGIN
test=# delete from change where id || ':' || datum not in (select id || ':' || max(datum) from change group by id order by 1);
DELETE 4
test=# select * from change ;
id | datum | text
----+----------------------------+----------
1 | 2005-12-07 08:28:43.263171 | foo_last
2 | 2005-12-07 08:28:55.819969 | foo_last
(2 rows)

But i'm not sure if this works correctly for you.

HTH, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===

Thanks ,
this one works beautifully.

Alex

PS: also thanks to the other suggestions, have a look at this one

hubert depesz lubaczewski wrote:

Hi All!

Tell me please - is there any script for conversion MySQL dump
file into PostgreSQL ?

Thanks!

Igor.

Postgresql supports records in the where clause i.e. you can compare
multiple columns simultaneously:

test=# delete from change where id || ':' || datum not in (select id ||

':' || max(datum) from change group by id order by 1);

could therefore be rewritten to:

delete from change where (id, datum) in (select id, max(datum) from change
group by id);

Yours,

Aarjan Langereis

----- Original Message -----
From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
To: <pgsql-general@postgresql.org>
Sent: Wednesday, December 07, 2005 8:47 AM
Subject: Re: [GENERAL] Delete Question

am 07.12.2005, um 18:21:25 +1100 mailte Alex folgendes:

Hi,

I have a table where I store changes made to an order. The looks like
ProdID, ChangeDate, Change1, Change2, ... etc.
Some ProdIDs have multiple records.

Is there an easy way to delete all records of a ProdID except the most
recent (ChangeDate is timestamp) one? Preferably in one SQL statement?

test=# select * from change ;
id | datum | text
----+----------------------------+----------
1 | 2005-12-07 08:28:28.939312 | foo
1 | 2005-12-07 08:28:34.695091 | foo2
1 | 2005-12-07 08:28:37.150354 | foo3
1 | 2005-12-07 08:28:43.263171 | foo_last
2 | 2005-12-07 08:28:48.419252 | foo
2 | 2005-12-07 08:28:55.819969 | foo_last
(6 rows)

test=# begin;
BEGIN
test=# delete from change where id || ':' || datum not in (select id ||

':' || max(datum) from change group by id order by 1);

DELECT FROM table
WHERE (ProdID,ChangeDate) not in
(SELECT ProdID,MAX(ChangeDate) FROM table
GROUP BY ProdID)

I hope this works.

On 12/7/05, Alex <alex@meerkatsoft.com> wrote:

Hi,

I have a table where I store changes made to an order. The looks like
ProdID, ChangeDate, Change1, Change2, ... etc.
Some ProdIDs have multiple records.

Is there an easy way to delete all records of a ProdID except the most
recent (ChangeDate is timestamp) one? Preferably in one SQL statement?

Thanks for any advise

Alex

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

--
Regards
Pandu

am 07.12.2005, um 8:54:56 +0100 mailte A.j. Langereis folgendes:

Postgresql supports records in the where clause i.e. you can compare
multiple columns simultaneously:

test=# delete from change where id || ':' || datum not in (select id ||

':' || max(datum) from change group by id order by 1);

could therefore be rewritten to:

delete from change where (id, datum) in (select id, max(datum) from change
group by id);

Oh, thank you!

I have tried this way, but with a wrong syntax.

Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===

am 07.12.2005, um 10:53:27 +0300 mailte go KEY802207 folgendes:

Hi All!

Tell me please - is there any script for conversion MySQL dump
file into PostgreSQL ?

http://techdocs.postgresql.org/#convertfrom

HTH, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===