Delete duplicates without using RowId

Hi,

Is there a way of deleting duplicate records without using the rowId column.

Actually I want to delete duplicates in a sybase database.

Thanks

Assuming you have a PK, count by your PK and delete the min() or max().

Quote:

---

Originally posted by marist89
Assuming you have a PK, count by your PK and delete the min() or max().

---

Nope there is no primary key.

Quote:

---

Originally posted by ronnie
Nope there is no primary key.

---

How do you know you have duplicates, then?

Quote:

---

Originally posted by marist89

Quote:

---

Originally posted by ronnie
Nope there is no primary key.

---

How do you know you have duplicates, then?

---

select account_id, trade_date, settlement_date, amount, quantity
from trades
group by account_id, trade_date, settlement_date, amount, quantity
having count(*) > 1

Oh, so you do have a PK.

Using the exact query you specified, I would create a temporary table of all my unique combinations. Then, I would figure out which row I want to drop with a min/max function and put that in the temporary table too. Once I knew the rows I wanted to delete, I would delete them from my base table.

Quote:

---

Originally posted by marist89
Oh, so you do have a PK.

Using the exact query you specified, I would create a temporary table of all my unique combinations. Then, I would figure out which row I want to drop with a min/max function and put that in the temporary table too. Once I knew the rows I wanted to delete, I would delete them from my base table.

---

Jeff,
i dont have a PK on the table at all. It can have multiple records with the same account Id .

Can it be done using one query/delete statement

Thanks

could you not create a table as select distinct (*) from original table and then either
a) drop the original table and rename the new one (is this possible)?
or
b) truncate the original tables and populate it from the new table.

A table without PK is not a table at all.

Quote:

---

Originally posted by tamilselvan
A table without PK is not a table at all.

---

what if those are the requirements and you have to delete the data only after certain checks and balances are taken care of. :-)

Quote:

---

Originally posted by ronnie

select account_id, trade_date, settlement_date, amount, quantity
from trades
group by account_id, trade_date, settlement_date, amount, quantity
having count(*) > 1

---

in oracle we use

delete trades a
where rowid = (select max(rowid) from trades where
account_id = a.account_id and trade_date = a. trade_date and settlement_date = a.settlement_date and amount = a.amount and quantity = a.quantity )

so if you dont want to use rowid you can create a temporary field with unique values ( may be generated withsequence ) and use that value instead of rowid.

===============================
Posted by Ronnie

what if those are the requirements and you have to delete the data only after certain checks and balances are taken care of. :-)

=======================================

The verification of checks and balances should be taken care of your application.
As per the RDBMS theory (principle), every row must be uniquely identified by a key. That is why we create PK on all tables.

There is an exception, i.e for temporary table. In which we truncate the table after its usage.

A real and good banking application would check the current balance with the balance at the time of transaction started.
Example:
Time-00: Transaction started Balance $1000.00
Store $1000 to a variable
Time-01: Subtract $800.00
Time-02: Update acct_table
set balance = balance - $800
where ac_id = id_num
and balance = $1000;
Time-03 if update fails, then some one has already withdraw money from the a/c. The balance is not $1000.Hence, the current transaction will not be executed. Raise error

[Edited by tamilselvan on 05-22-2002 at 02:10 PM]

Quote:

---

Originally posted by mrchrispy
could you not create a table as select distinct (*) from original table and then either
a) drop the original table and rename the new one (is this possible)?
or
b) truncate the original tables and populate it from the new table.

---

yeah, why can't you do this? A distinct list eliminates the duplicates ...

- Magnus

Yes I can do that and eventually will do that.

I was just wondering if there is a way to do it in a single statement and I guess that there is'nt.

Thanks