Author: bj东方瑞通

Methods of querying duplicate data and deleting duplicate records in Oracle

[Copy link]

0

Threads

0

Posts

0

Credits

Guest

Credits
0
Post time 5-31-2012 14:17:13 | Show all posts |Read mode


For example, there is a personnel table (Table Name:peosons)
If you want to record the name, ID number and address of the three fields exactly the same,
select p1.*
from persons p1,persons p2
where p1.id<>p2.id
and  p1.cardid = p2.cardid and p1.pname = p2.pname and p1.address = p2.address
use rowid Method can achieve the above effect.
According to oracle With rowid Property to determine whether there is repetition. The statement is as follows:
Check the data:
select * from table1 a where rowid !=(select max(rowid)
from table1 b where a.name1=b.name1 and a.name2=b.name2……)
Delete data:
delete  from table1 a where rowid !=(select max(rowid)
from table1 b where a.name1=b.name1 and a.name2=b.name2……)
Reply

Use magic Report

0

Threads

1175

Posts

1169

Credits

L5-Colonel

Rank: 6Rank: 6

Credits
1169
Post time 4-2-2024 01:59:54 | Show all posts
thanks for sharing this
Reply

Use magic Report

0

Threads

637

Posts

634

Credits

L4-Major

Rank: 4

Credits
634
Post time 4-21-2024 17:34:29 | Show all posts
Many thanks
Reply

Use magic Report

0

Threads

1262

Posts

1259

Credits

L5-Colonel

Rank: 6Rank: 6

Credits
1259
Post time 6-25-2024 23:23:12 | Show all posts
good job
Reply

Use magic Report

0

Threads

1373

Posts

1363

Credits

L5-Colonel

Rank: 6Rank: 6

Credits
1363
Post time 6-26-2024 10:23:47 | Show all posts
thank you
Reply

Use magic Report

0

Threads

767

Posts

766

Credits

L4-Major

Rank: 4

Credits
766
Post time 8-1-2024 09:38:29 | Show all posts
I need it, thanks
Reply

Use magic Report

0

Threads

1716

Posts

1703

Credits

L5-Colonel

Rank: 6Rank: 6

Credits
1703
Post time 8-1-2024 11:57:35 | Show all posts
thank you so much
Reply

Use magic Report

0

Threads

1072

Posts

1069

Credits

L5-Colonel

Rank: 6Rank: 6

Credits
1069
Post time 8-1-2024 12:25:39 | Show all posts
good material
Reply

Use magic Report

0

Threads

961

Posts

951

Credits

L4-Major

Rank: 4

Credits
951
Post time 8-9-2024 23:19:17 | Show all posts
Thanks
Reply

Use magic Report

0

Threads

1473

Posts

1454

Credits

L5-Colonel

Rank: 6Rank: 6

Credits
1454
Post time 8-13-2024 22:56:01 | Show all posts
good material
Reply

Use magic Report

You have to log in before you can reply Login | Register Now

Points Rules

Quick Reply Contact us with Whastsapp Contact us with Teams Contact us with Telegram To Top Return to the list