deleting duplicate rows - SQL anywhere

You can do it in a single statement if you have something to make them distinct (like a timestamp) otherwise you can do it with a loop.

with a timestamp

DELETE yourtable
FROM yourtable a
WHERE EXISTS (SELECT * FROM yourtable b WHERE b.name1 = a.name1 AND b.age1 = a.age1 AND b.timestamp > a.timestamp)

without a timestamp

SET ROWCOUNT 1

DELETE yourtable
FROM yourtable a
WHERE (SELECT COUNT(*) FROM yourtable b WHERE b.name1 = a.name1 AND b.age1 = a.age1) > 1

WHILE @@rowcount > 0
    DELETE yourtable
    FROM yourtable a
    WHERE (SELECT COUNT(*) FROM yourtable b WHERE b.name1 = a.name1 AND b.age1 = a.age1) > 1

SET ROWCOUNT 0

suggest you close the duplicate question
https://www.experts-exchange.com/questions/20805738/deleting-duplicate-rows-SQL-anywhere.html

did you have any luck with this ?

I'm not sure is Anywhere allows temporary tables, other wise you could try this (given the fact that the 2 columns in the tabel are the only ones).

select distinct *
into   #table
from  your_table

delete your_table

insert into your_table
select * from #table

Not a single statement, but you can't do it in a single statement if you don't have any other columns in the table.

If you have a PK you can use this simple SQL:

DELETE FROM mytable
WHERE EXISTS
 (  SELECT name1, age1, MIN(id) as cf_prog
    FROM mytable TAB02
WHERE ( mytable.name1 = TAB02.name1 ) AND
            ( mytable.age1 = TAB02.age1 )
GROUP BY name1, age1
  HAVING ( count(*) > 1 ) AND
               ( mytable.id <> cf_prog )   )
           ;

jothiramm

did your get what you were after ?

jothiramm
please return to the question

sorry..

nothing, satisfies.. and i thought this is not possible in Anywhere..

thanks.

why do you say "nothing, satisfies", all of theses solutions will remove duplicate rows

s

but i want to know, WITHIN single statement, i have to do (pls. refer my question)

table structure is shown samething (shown coloums only there in table)

1. Via temporary table. As ahoor did.
2.
   a. Add identity column ID
           alter table tbl add id numeric(18,0) identity not null
   b. Execute query:
           delete from tbl where exists
           (select 1 from tbl t2 where tbl.login = t2.login and tbl.age = t2.age and tbl.id > t2.id)
   c. Remove identity column ID
           alter table tbl drop id

P.S. DO NOT NEVER DESIGN TABLE WITHOUT PK!!!!

No comment has been added to this question in more than 21 days, so it is now classified as abandoned..
I will leave the following recommendation for this question in the Cleanup topic area:

Answered ChrisKing

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

leonstryker
EE Cleanup Volunteer