Oracle delete delete Record SQL statement usage

The syntax is the same as the other SQL database tutorials, as follows

DELETE from Courses

WHERE course_designater = ' Java110 '

A more complex method of operation by condition

DELETE from Classconvenings

WHERE CLASSES_NUM_FK > 4

and class_convene_date = To_date (' 2006-02-15 00:00:00 ',

' Yyyy-mm-dd HH24:MI:SS ')

and CLASS_LOCATION_FK = ' seattle-training Room 1 '

Let's take a look at a detailed example

Sql>--Create demo table

Sql> CREATE TABLE Employee (

2 ID VARCHAR2 (4 BYTE) not NULL,

3 first_name VARCHAR2 (BYTE),

4 last_name VARCHAR2 (BYTE),

5 start_date Date,

6 end_date Date,

7 Salary Number (8,2),

8 City VARCHAR2 (BYTE),

9 Description VARCHAR2 (MB)

10)

11/

Table created.

Sql>

Sql>--Prepare data

sql> INSERT into Employee (IDs, first_name, last_name, start_date, End_date, Salary, City, Description)

2 values (' ', ' Jason ', ' Martin ', to_date (' 19960725 ', ' YYYYMMDD '), to_date (' 20060725 ', ' YYYYMMDD '), 1234.56, ' Toronto ', ' Programmer ')

3/

1 row created.

sql> INSERT into Employee (IDs, first_name, last_name, start_date, End_date, Salary, City, Description)

2 values (' ', ' Alison ', ' Mathews ', To_date (' 19760321 ', ' YYYYMMDD '), to_date (' 19860221 ', ' YYYYMMDD '), 6661.78, ' Vancouver ', ' Tester ')

3/

1 row created.

sql> INSERT into Employee (IDs, first_name, last_name, start_date, End_date, Salary, City, Description)

2 values (', ' James ', ' Smith ', to_date (' 19781212 ', ' YYYYMMDD '), to_date (' 19900315 ', ' YYYYMMDD '), 6544.78, ' Vancouver ' , ' Tester ')

3/

1 row created.

sql> INSERT into Employee (IDs, first_name, last_name, start_date, End_date, Salary, City, Description)

2 values (' ', ' Celia ', ' Rice ', to_date (' 19821024 ', ' YYYYMMDD '), to_date (' 19990421 ', ' YYYYMMDD '), 2344.78, ' Vancouver ', ' Manager ')

3/

1 row created.

sql> INSERT into Employee (IDs, first_name, last_name, start_date, End_date, Salary, City, Description)

2 values (' ', ' Robert ', ' Black ', to_date (' 19840115 ', ' YYYYMMDD '), to_date (' 19980808 ', ' YYYYMMDD '), 2334.78, ' Vancouver ', ' Tester ')

3/

1 row created.

sql> INSERT into Employee (IDs, first_name, last_name, start_date, End_date, Salary, City, Description)

2 values (' ', ' Linda ', ' Green ', to_date (' 19870730 ', ' YYYYMMDD '), to_date (' 19960104 ', ' YYYYMMDD '), 4322.78, ' New York ', ' Tester ')

3/

1 row created.

sql> INSERT into Employee (IDs, first_name, last_name, start_date, End_date, Salary, City, Description)

2 values (' Modified ', ' David ', ' Larry ', to_date (' 19901231 ', ' YYYYMMDD '), to_date (' 19980212 ', ' YYYYMMDD '), 7897.78, ' New York ', ' Manager ')

3/

1 row created.

sql> INSERT into Employee (IDs, first_name, last_name, start_date, End_date, Salary, City, Description)

2 values (', ' James ', ' Cat ', to_date (' 19960917 ', ' YYYYMMDD '), to_date (' 20020415 ', ' YYYYMMDD '), 1232.78, ' Vancouver ', ' Tester ')

3/

1 row created.

Sql>

Sql>

Sql>

Sql>--Display data in the table

Sql> SELECT * from Employee

2/

ID first_name last_name start_dat end_date SALARY City DESCRIPTION

---- ---------- ---------- --------- --------- ---------- ---------- ---------------

Jason Martin 25-jul-96 25-jul-06 1234.56 Toronto Programmer

Alison Mathews 21-mar-76 21-feb-86 6661.78 Vancouver Tester

James Smith 12-dec-78 15-mar-90 6544.78 Vancouver Tester

Celia Rice 24-oct-82 21-apr-99 2344.78 Vancouver Manager

Robert Black 15-jan-84 08-aug-98 2334.78 Vancouver Tester

Linda Green 30-jul-87 04-jan-96 4322.78 New York Tester

David Larry 31-dec-90 12-feb-98 7897.78 New York Manager

ID first_name last_name start_dat end_date SALARY City DESCRIPTION

---- ---------- ---------- --------- --------- ---------- ---------- ---------------

James Cat 17-sep-96 15-apr-02 1232.78 Vancouver Tester

8 rows selected.

Sql>

Sql>

Sql>

Sql>

Sql>

Sql>

sql>--delete command with conditions

Sql>

Sql> Delete from Employee where Salary > 3000;

4 rows deleted.

Sql>

Sql> select * from Employee;

ID first_name last_name start_dat end_date SALARY City DESCRIPTION

---- ---------- ---------- --------- --------- ---------- ---------- ---------------

Jason Martin 25-jul-96 25-jul-06 1234.56 Toronto Programmer

Celia Rice 24-oct-82 21-apr-99 2344.78 Vancouver Manager

Robert Black 15-jan-84 08-aug-98 2334.78 Vancouver Tester

James Cat 17-sep-96 15-apr-02 1232.78 Vancouver Tester

4 rows selected.

Deletes the specified content

Sql> CREATE TABLE Project (

2 pro_id Number (4),

3 Pro_name VARCHAR2 (40),

4 Budget Number (9,2),

5 CONSTRAINT project_pk PRIMARY KEY (pro_id)

6);

Table created.

Sql>

Sql>

Sql> INSERT into Project (pro_id, Pro_name, Budget) VALUES (1001, ' A ', 1912000);

1 row created.

Sql> INSERT into Project (pro_id, Pro_name, Budget) VALUES (1002, ' ERP ', 9999999);

1 row created.

Sql> INSERT into Project (pro_id, Pro_name, Budget) VALUES (1003, ' SQL ', 897000);

1 row created.

Sql> INSERT into Project (pro_id, Pro_name, Budget) VALUES (1004, ' CRM ', 294000);

1 row created.

Sql> INSERT into Project (pro_id, Pro_name, Budget) VALUES (1005, ' VPN ', 415000);

1 row created.

Sql>

Sql>

Sql> SET ECHO on

Sql> DELETE

2 from Project

3 WHERE pro_id = 1006

Note : Please pay attention to the triple programming Tutorials section for more wonderful articles .