Solved

deleting duplicate rows - SQL anywhere

Posted on 2003-11-21
14
3,984 Views
Last Modified: 2012-05-04
hello all,

is anybody know, in SQL anywhere, how to delete dublicate rows in single query itself..

table design is follows..

name1    age1
----------------
asd          13
asd          14
asd          13

i want the data in table as follows.. (by removing duplicate rows)

name1    age1
----------------
asd         13
asd         14

how we can do this, with single query itself...?
regrds
jothiram
0
Comment
Question by:jothiramm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
14 Comments
 
LVL 6

Expert Comment

by:ChrisKing
ID: 9802482
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
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 9802534
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 9810490
did you have any luck with this ?
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 3

Expert Comment

by:ahoor
ID: 9832519
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.
0
 
LVL 6

Expert Comment

by:DaniPro
ID: 9835885
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 )   )
           ;

0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 9849587
jothiramm

did your get what you were after ?
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 9963519
jothiramm
please return to the question
0
 

Author Comment

by:jothiramm
ID: 9964016
sorry..

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

thanks.
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 9967913
why do you say "nothing, satisfies", all of theses solutions will remove duplicate rows
0
 

Author Comment

by:jothiramm
ID: 9970591
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)
0
 
LVL 6

Accepted Solution

by:
ChrisKing earned 100 total points
ID: 9970939
actually you asked for a single "query" and a query can be multiple statements executed in a single batch.

but, this is a single statement

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)

and so is this

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 )   )
0
 

Expert Comment

by:hredwolf
ID: 10210602
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!!!!
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11503886
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
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Check out this step-by-step guide for asking an anonymous question on Experts Exchange.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question