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