Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to delete heavy data in oracle. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something from this article.

Preparation in advance

Create a test table

Create table salary (

Staffid int

Staff varchar (15)

);

Simulated duplicate data

Insert into salary values (1)

Insert into salary values (2)

Insert into salary values (3)

Insert into salary values (4)

Insert into salary values (5 ~ (th))

Insert into salary values (1)

Insert into salary values (2)

Insert into salary values (3)

Insert into salary values (4)

Insert into salary values (5 ~ (th))

Insert into salary values (1)

Insert into salary values (2)

Insert into salary values (3)

Insert into salary values (4)

Insert into salary values (5 ~ (th))

Insert into salary values (10 Magna aaaaa')

Insert into salary values (20)

Insert into salary values (30dwt')

Insert into salary values (40 dsd`)

Insert into salary values (50df`)

Insert into salary values (1memoookkkk')

Experiment 1: simulate data duplication in a single field

Select * from salary

STAFFID STAFF

--

1 oookkk

1 a

2 s

3 ert

4 d

5 b

1 a

2 s

3 ert

4 d

5 b

1 a

2 s

3 ert

4 d

5 b

10 aaaa

20 sass

30 erwt

40 dsd

50 bsdf

21 rows selected

1. Find out duplicate data

Method one

SELECT *

FROM salary a

WHERE (SELECT COUNT (*)

FROM salary

WHERE staffid = a.staffid) > 1)

ORDER BY staffid

Method two

Select *

From salary

Where staffid in

(select staffid from salary group by staffid having count (staffid) > 1)

Delete duplicate data, keep only one item, and delete all the rest

Method 1, delete via rowid

Delete from salary

Where staffid in (select staffid from salary group by staffid having count (staffid) > 1)

And rowid not in (select min (rowid) from salary group by staffid having count (staffid) > 1)

Experiment 2: simulate the duplicate data of two fields

1. Query duplicate records

Method one

Select * from salary a

Where (a. Organizid.staff) in (select staffid,staff from salary group by staffid,staff having count (*) > 1)

Method two

SELECT *

FROM salary a

WHERE (SELECT COUNT (*)

FROM salary

WHERE staffid = a.staffid and staff=a.staff) > 1)

ORDER BY staffid

As a result, a total of 15 items

STAFFIDSTAFF

1 a

1 a

1 a

2 s

2 s

2 s

3 ert

3 ert

3 ert

4 d

4 d

4 d

5 b

5 b

5 b

two。 Delete duplicate data, keep only one item, and delete all the rest

Delete from vitae a

Where (a.peopleIdline a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count (*) > 1)

And rowid not in (select min (rowid) from vitae group by peopleId,seq having count (*) > 1)

3. View the results after deletion

Select * from salary

Result

STAFFIDSTAFF

1 oookkk

1 a

2 s

3 ert

4 d

5 b

10 aaaa

20 sass

30 erwt

40 dsd

50 bsdf

After reading the above, do you have any further understanding of how to delete heavy data in oracle? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.