Thread: Delete Question

Delete Question

From

Alex

Date:

07 December 2005, 06:21:29

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

Re: Delete Question

From

hubert depesz lubaczewski

Date:

07 December 2005, 06:27:59

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

Re: Delete Question

From

Michael Glaesemann

Date:

07 December 2005, 06:35:16

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

Re: Delete Question

From

"A. Kretschmer"

Date:

07 December 2005, 06:47:06

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
by1); 
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    ===

Re: Delete Question

From

Alex

Date:

07 December 2005, 06:52:11

Thanks ,
this one works beautifully.

Alex

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


hubert depesz lubaczewski wrote:

> On 12/7/05, *Alex* <alex@meerkatsoft.com
> <mailto: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

Re: Delete Question

From

Pandurangan R S

Date:

07 December 2005, 06:55:22

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

Re: Delete Question

From

"A.j. Langereis"

Date:

07 December 2005, 06:58:32

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);
> 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    ===
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>

Re: Delete Question

From

"A. Kretschmer"

Date:

07 December 2005, 07:02:10

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    ===

MySQL dump

From

go KEY802207

Date:

07 December 2005, 07:54:58

Hi All!

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


 Thanks!

 Igor.

Re: MySQL dump

From

"A. Kretschmer"

Date:

07 December 2005, 08:07:06

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    ===