View: 1292|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

576

Posts

570

Credits

L4-Major

Rank: 4

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

944

Posts

934

Credits

L4-Major

Rank: 4

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

Use magic Report

0

Threads

458

Posts

457

Credits

L3-captain

Rank: 3Rank: 3

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

Use magic Report

0

Threads

1334

Posts

1321

Credits

L5-Colonel

Rank: 6Rank: 6

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

Use magic Report

0

Threads

583

Posts

580

Credits

L4-Major

Rank: 4

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

1197

Posts

1178

Credits

L5-Colonel

Rank: 6Rank: 6

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