Tuesday, March 27, 2012

clear identical submissions

i want to clear from my db the doubles and trebles submisions and keep a unique.my db looks like:

a.a code lastname firstname phone
----------------
1 101 smith john 23452345
2 101 smith john 23452345
3 123 black mary 57645756
4 654 white peter 45634564

i want to look like

a.a code lastname firstname phone
----------------
1 101 smith john 23452345
3 123 black mary 57645756
4 654 white peter 45634564

thank you .angeloOracle 9i,

delete from table
where NOT (rowid = (select min(rowid) from table group by KEY));

KEY is defined as a candidate key that is not enforced.|||dear sir
thank you for your reply.

message returned: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

thank you in advance|||Select distinct columns into #temp from tableA
delete from tableA
insert into tableA select * from #temp
drop table #temp
alter table tableA add primary key (columns)|||I'd use something like:DELETE FROM table
WHERE EXISTS (SELECT *
FROM table AS b
WHERE b.id < table.id
AND b.code = table.code
AND b.lastname = table.lastname
AND b.firstname = table.firstname
AND b.phone = table.phone)

Note that I used the php tag to get a monospaced font, this is just standard SQL-92 syntax that should run on any SQL based product.

-PatP

No comments:

Post a Comment