Insert Update Delete

Insert

Usage:

INSERT INTO tablename [(COLUM1,COLUM2, ...)] VALUES (VALUE1,VALUE2, ...);

After accessing the scott account with SQL * PLUS, run the following:

INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
VALUES (1562,'Alison','SALESMAN',7698,SYSDATE,2000,1000,30);

New employee data has been inserted. If you supply all the values in order according to the table structure, you can omit the column list in parentheses after the table name. You can omit '' for column values of type NUMBER. SYSDATE is used by Oracle to obtain the current date data.

INSERT INTO EMP 
VALUES (1500,'Bill','SALESMAN',7698,SYSDATE,1800,1200,30);

The employee number is the primary key. Therefore, an employee with the same employee number can not exist in the table.

INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO)
VALUES (1697,'Carol','MANAGER',7839,SYSDATE,3000,30);

COMM is missing from the column list. There is no value for COMM column also. This inserts NULL into the COMM column. This is one way to enter NULL into a column.

Update

Usage:

UPDATE tableName 
SET column1='value1', column2='value2', ...
[WHERE Conditions]

SET: Used to change the value of a specific column with the UPDATE statement.
The value can be a number or a character, or a combination of an operable column and an operator.
[WHERE Conditions]: Used to change the value of a column that meets the condition.
If you omit the WHERE condition in the UPDATE statement, all records in the table are changed.

Change Bill's salary to 2800.

UPDATE EMP 
SET SAL=2800
WHERE ENAME='Bill';

Increase Alison's salary by 300 and change COMM to 500.

UPDATE EMP 
SET SAL=SAL + 300,COMM=500
WHERE ENAME='Alison';

To manage past INSERT and UPDATE statements as permanent data, use COMMIT;

COMMIT;

Delete

Usage:

DELETE FROM tableName 
[Where Conditions]

If you omit the WHERE statement in the DELETE statement, all records in the table are deleted.

Delete Alison, Bill, and Carol from the Employee table.

DELETE FROM EMP 
WHERE ENAME IN ('Alison','Bill','Carol');