deleting an identical record
In a table I entered by accident two times the same record, if I now
list the table I see two lines with exactly the same contents. How can
I delete one of those two records? I can't find a select criterium which
differs for both.
regards,
Hugo
In a table I entered by accident two times the same record, if I now
list the table I see two lines with exactly the same contents. How can
I delete one of those two records? I can't find a select criterium which
differs for both.
create table t (a int, b text);
insert into t values (1, 'a');
insert into t values (1, 'a');
insert into t values (1, 'a');
insert into t values (1, 'a');
select oid, * from t;
Lee Harr wrote:
In a table I entered by accident two times the same record, if I now
list the table I see two lines with exactly the same contents. How can
I delete one of those two records? I can't find a select criterium which
differs for both.create table t (a int, b text);
insert into t values (1, 'a');
insert into t values (1, 'a');
insert into t values (1, 'a');
insert into t values (1, 'a');select oid, * from t;
thanks, this works indeed, what's the meaning of the comma behind oid?
On Thu, 2002-02-21 at 03:44, Lee Harr wrote:
In a table I entered by accident two times the same record, if I now
list the table I see two lines with exactly the same contents. How can
I delete one of those two records? I can't find a select criterium which
differs for both.create table t (a int, b text);
insert into t values (1, 'a');
insert into t values (1, 'a');
insert into t values (1, 'a');
insert into t values (1, 'a');select oid, * from t;
Aren't the oids different?
DELETE FROM t WHERE oid <> xxx
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
"My sheep hear my voice, and I know them, and they
follow me; And I give unto them eternal life; and they
shall never perish, neither shall any man pluck them
out of my hand." John 10:27,28
Oliver Elphick wrote:
On Thu, 2002-02-21 at 03:44, Lee Harr wrote:
In a table I entered by accident two times the same record, if I now
list the table I see two lines with exactly the same contents. How can
I delete one of those two records? I can't find a select criterium which
differs for both.create table t (a int, b text);
insert into t values (1, 'a');
insert into t values (1, 'a');
insert into t values (1, 'a');
insert into t values (1, 'a');select oid, * from t;
Aren't the oids different?
DELETE FROM t WHERE oid <> xxx
OUCH !!! There will be only one row left in the table. Be carefull.
On Thu, 2002-02-21 at 09:25, Jean-Luc Lachance wrote:
Oliver Elphick wrote:
create table t (a int, b text);
insert into t values (1, 'a');
insert into t values (1, 'a');
insert into t values (1, 'a');
insert into t values (1, 'a');select oid, * from t;
Aren't the oids different?
DELETE FROM t WHERE oid <> xxx
OUCH !!! There will be only one row left in the table. Be carefull.
True. But in my defence, that is valid in the context of this
particular example. (Nevertheless, I was right only by accident and
your warning is very necessary.)
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
"My sheep hear my voice, and I know them, and they
follow me; And I give unto them eternal life; and they
shall never perish, neither shall any man pluck them
out of my hand." John 10:27,28
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
In a table I entered by accident two times the same record, if I now
list the table I see two lines with exactly the same contents. How can
I delete one of those two records? I can't find a select criterium which
differs for both.
CREATE TABLE t (foo INT, bar INT);
INSERT INTO t VALUES (2,4);
INSERT INTO t VALUES (2,4);
INSERT INTO t VALUES (2,4);
INSERT INTO t VALUES (2,4);
INSERT INTO t VALUES (2,4);
To delete just one of the duplicates:
DELETE FROM t WHERE oid = (SELECT oid FROM t WHERE foo=2 AND bar=4 LIMIT 1);
To delete the last one added:
DELETE FROM t WHERE oid =
(SELECT oid FROM t WHERE foo=2 AND bar=4 ORDER BY oid DESC LIMIT 1);
Unfortunately, the above will also delete a single record with those
conditions, so we should make sure there are at least two records first:
DELETE FROM t WHERE oid =
(SELECT oid FROM t WHERE foo=2 AND bar=4 ORDER BY oid DESC LIMIT 1)
AND oid !=
(SELECT oid FROM t WHERE foo=2 AND bar=4 ORDER BY oid ASC LIMIT 1);
but that gets a little ugly. Why not delete all but the first one we added?:
DELETE FROM t WHERE foo=2 AND bar=4 AND
oid != (SELECT oid FROM t WHERE foo=2 AND bar=4 ORDER BY oid ASC LIMIT 1);
The above should work for all cases.
HTH,
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200202210848
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html
iD8DBQE8dPvyvJuQZxSWSsgRAohaAJoCssDevWjvWRRB5Qwse7XJrGUp0gCgz1nI
okDJcYTpVLjiRv8+zYlYlb0=
=WEOm
-----END PGP SIGNATURE-----
Import Notes
Resolved by subject fallback
This conversation reminds me of a 'helpful' little 'feature' in MS Access. Their advice for deleting duplicate records is to create a copy of the table with unique index on the fields that you don't want to be duplicated, then insert into table2 select * from table1. They will happily delete all but one of them for you. I don't know if it's the first, last, or a random record that they keep.
Microsoft Access,You Don't Have to Know What You are Doing (TM).
"Greg Sabino Mullane" <greg@turnstep.com> 02/21/02 05:55AM >>>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
In a table I entered by accident two times the same record, if I now
list the table I see two lines with exactly the same contents. How can
I delete one of those two records? I can't find a select criterium which
differs for both.
CREATE TABLE t (foo INT, bar INT);
INSERT INTO t VALUES (2,4);
INSERT INTO t VALUES (2,4);
INSERT INTO t VALUES (2,4);
INSERT INTO t VALUES (2,4);
INSERT INTO t VALUES (2,4);
To delete just one of the duplicates:
DELETE FROM t WHERE oid = (SELECT oid FROM t WHERE foo=2 AND bar=4 LIMIT 1);
To delete the last one added:
DELETE FROM t WHERE oid =
(SELECT oid FROM t WHERE foo=2 AND bar=4 ORDER BY oid DESC LIMIT 1);
Unfortunately, the above will also delete a single record with those
conditions, so we should make sure there are at least two records first:
DELETE FROM t WHERE oid =
(SELECT oid FROM t WHERE foo=2 AND bar=4 ORDER BY oid DESC LIMIT 1)
AND oid !=
(SELECT oid FROM t WHERE foo=2 AND bar=4 ORDER BY oid ASC LIMIT 1);
but that gets a little ugly. Why not delete all but the first one we added?:
DELETE FROM t WHERE foo=2 AND bar=4 AND
oid != (SELECT oid FROM t WHERE foo=2 AND bar=4 ORDER BY oid ASC LIMIT 1);
The above should work for all cases.
HTH,
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200202210848
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html
iD8DBQE8dPvyvJuQZxSWSsgRAohaAJoCssDevWjvWRRB5Qwse7XJrGUp0gCgz1nI
okDJcYTpVLjiRv8+zYlYlb0=
=WEOm
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Import Notes
Resolved by subject fallback
Hugo Coolens <hugo.coolens@skynet.be> writes:
Lee Harr wrote:
select oid, * from t;
thanks, this works indeed, what's the meaning of the comma behind oid?
If you want oid as a result column in a select, you need to specify it
explicitly--it's not part of '*'.
-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863
Import Notes
Reply to msg id not found: HugoCoolens'smessageofThu21Feb2002071849+0100
create table t (a int, b text);
insert into t values (1, 'a');
insert into t values (1, 'a');
insert into t values (1, 'a');
insert into t values (1, 'a');select oid, * from t;
thanks, this works indeed, what's the meaning of the comma behind oid?
select
a,
b,
sqrt(a::numeric),
'a: '||a::text||' B: '||b::text as myfield
from
t;
SELECT chooses fields from a table. When you select *, you choose all
fields (except for oid...). You can choose functions and operate
on fields too.
The ::type notation is to coerce the value in to the right type
for the particular function or operator.
So. The comma means "these are separate items in a list of fields."
On Thu, 2002-02-21 at 00:58, Hugo Coolens wrote:
In a table I entered by accident two times the same record, if I now
list the table I see two lines with exactly the same contents. How can
I delete one of those two records? I can't find a select criterium which
differs for both.
The short answer is pgaccess...
Cheers
Tony Grant
--
RedHat Linux on Sony Vaio C1XD/S
http://www.animaproductions.com/linux2.html
Macromedia UltraDev with PostgreSQL
http://www.animaproductions.com/ultra.html