Delete duplicate in a single query

Database
Highlighted
Enthusiast

Delete duplicate in a single query

How to delete duplicates from a table in a single SQL without creating another temo table?

A B C

1 2 3

4 5 6

7 8 9

4 5 6

4 REPLIES
Enthusiast

Re: Delete duplicate in a single query

sel

A,B,C

FROM

table

group by A,B,C .

Enthusiast

Re: Delete duplicate in a single query

Thanks Reddy for your reply. I need the delete SQL. After executing the SQL table(T1) will look like:

Table: T1

A B C

1 2 3

4 5 6

7 8 9

Enthusiast

Re: Delete duplicate in a single query

Moutusi,

Since ABC is your entire record a single SQL can't distinguish one record from the other with the same three values. You will delete both records.

Inserting into a set table is simple but if you don't have the access to create a new table

You could create a volatile table(set is default) and insert all rows into it, this will get rid of duplicates, then delete all records from TI , then insert  back into your multiset table. Creating a volatile table requires no create table access on a database or perm space.

Rglass

Enthusiast

Re: Delete duplicate in a single query

You can also do thus:

create table tmp as

(select * from abc qualify row_number() over (partition by a,b,c order by a,b,c)=1) with data;