View: 1299|Reply: 16

Methods of querying duplicate data and deleting duplicate records in Oracle

[Copy link]

0

Threads

0

Posts

0

Credits

Guest

Credits
0
Post time 31-5-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

577

Posts

571

Credits

L4-Major

Rank: 4

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

Use magic Report

0

Threads

374

Posts

371

Credits

L3-captain

Rank: 3Rank: 3

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

Use magic Report

0

Threads

847

Posts

844

Credits

L4-Major

Rank: 4

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

Use magic Report

0

Threads

945

Posts

935

Credits

L4-Major

Rank: 4

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

Use magic Report

0

Threads

459

Posts

458

Credits

L3-captain

Rank: 3Rank: 3

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

Use magic Report

0

Threads

1339

Posts

1326

Credits

L5-Colonel

Rank: 6Rank: 6

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

Use magic Report

0

Threads

584

Posts

581

Credits

L4-Major

Rank: 4

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

Use magic Report

0

Threads

642

Posts

632

Credits

L4-Major

Rank: 4

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

Use magic Report

0

Threads

1202

Posts

1183

Credits

L5-Colonel

Rank: 6Rank: 6

Credits
1183
Post time 13-8-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 Skype Contact us with Whastsapp Contact us with Telegram Contact us with Line Contact us with Line Contact us with Line Contact us with Line To Top Return to the list