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

1321

Posts

1315

Credits

L5-Colonel

Rank: 6Rank: 6

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

Use magic Report

0

Threads

689

Posts

686

Credits

L4-Major

Rank: 4

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

Use magic Report

0

Threads

1286

Posts

1283

Credits

L5-Colonel

Rank: 6Rank: 6

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

Use magic Report

0

Threads

1384

Posts

1374

Credits

L5-Colonel

Rank: 6Rank: 6

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

Use magic Report

0

Threads

803

Posts

802

Credits

L4-Major

Rank: 4

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

Use magic Report

0

Threads

1761

Posts

1748

Credits

L5-Colonel

Rank: 6Rank: 6

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

Use magic Report

0

Threads

1134

Posts

1131

Credits

L5-Colonel

Rank: 6Rank: 6

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

Use magic Report

0

Threads

968

Posts

958

Credits

L4-Major

Rank: 4

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

Use magic Report

0

Threads

1507

Posts

1488

Credits

L5-Colonel

Rank: 6Rank: 6

Credits
1488
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