Deleting Multiple Rows Based on Multiple Columns

From: Rich Shepard <rshepard(at)appl-ecosys(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Deleting Multiple Rows Based on Multiple Columns
Date: 2011-08-13 18:37:01
Message-ID: alpine.LNX.2.00.1108131128490.1426@salmo.appl-ecosys.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Thanks to David J. I have a working script to locate multiple rows having
the same values in three candidate columns. I used an enhanced version of
this script to copy those duplicate (and triplicate) records to a clone of
the original table.

Now I would like to delete those duplicates from the original table in
either of two ways, but my Google searches have not produced hits where the
selection criteria for the DELETE has multiple columns.

If it is possible to leave one row with specific values in the columns
(loc_name, sample_date, param) and delete the additional ones, I'd like to
learn how to do so. I know that I'll have use for these techniques with
future data.

Else, I'd like to delete all those rows with multiple copies. Then I'll
manually remove the extra rows in emacs, and insert the remainder in the
original table.

The script I tried to to the latter is:

DELETE FROM chemistry
WHERE
(SELECT lab_nbr, loc_name, sample_date, param, quant, units, qa_qc,
easting, northing, remark
FROM chemistry
Natural Inner join (
SELECT loc_name, sample_date, param, Count(*) as duplicate_count
FROM chemistry
GROUP BY loc_name, sample_date, param) grouped
WHERE duplicate_count > 1);

But postgres wants a single column in the first SELECT.

TIA,

Rich

Responses

Browse pgsql-general by date

  From Date Subject
Next Message c k 2011-08-13 18:37:13 Re: Using Postgresql as application server
Previous Message Pavel Stehule 2011-08-13 18:31:18 Re: Functions returning setof record -- can I use a table type as my return type hint?