An example of an SQL statement that automatically adds an ID to an Oracle database

The main content of this article includes: Implement Auto-incremental ID in Oracle and delete repeated records in the data table.

I. Auto-incrementing ID

1. First create sequence

Create sequence seqmax increment by 1

2. Get an ID

Select seqmax. nextval ID from dual

3. to delete a sequence

Drop sequence seqmax;

2. Delete duplicate records in a data table

1. Create a table first

Create TABLE "APPTEST "(

"ID" INTEGER primary key not null,

"MOBILE" nvarchar2 (50) NOT NULL

);

2. Assume that there are a large number of duplicate mobile phone numbers. To delete duplicate records, you can use either of the following methods:

(1) simple deletion using rowid

Delete from APPTEST a where rowid not in (select max (rowid) from APPTEST B where a. mobile = B. mobile );

It is said that this method is inefficient when the data volume is large.

(2) using analysis functions

Delete APPTEST where rowid in (

Select rid from

(Select rowid rid, row_number () over (partition by mobile order by id desc) rn from APPTEST)

Where rn> 1 );

(3) create a temp table