Delete repetitive records in traffic violation data table (at the same time [haptime], car number plate [Numberplate], penalties [REASON])
I. Method Principle: 1, Oracle, each record has a RowID, RowID is unique in the entire database, RowID determines which data file, block, line on each record is in Oracle.
2. In the repeated record, the contents of all columns may be the same, but the ROWID will not be the same, so as long as it is determined that the maximum ROWID has the maximum ROWID, the rest is removed.
Second, implementation methods: 1. Repeat record
Select Rowid, Haptime, Numberplate, Reason from Peccancy
--delete from peccancy6 peccancy6
Where peccancy.rowid! =
(
SELECT MAX (ROWID) from Peccancy B
Where peccancy.haptime = b.haptime and
peccancy.numberplate = B.Numberplate and
Peccancy.reason = B.reason
)
2. Delete repeat record
Delete from peccancy peccancy
Where peccancy.rowid! =
(
SELECT MAX (ROWID) from Peccancy B
Where peccancy.haptime = b.haptime and
peccancy.numberplate = B.Numberplate and
Peccancy.reason = B.reason
)
