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);

If you supply all the values according to the column order, you can omit the column list in parentheses after the table name. You can omit Single quotes for column value of type NUMBER. SYSDATE stores the current date data in Oracle.

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

The employee number is the primary key. Therefore, anohter 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);

There is no COMM from column list and no value for the COMM column from the value list, that inserts NULL into the COMM 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 character, or 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';

After executing INSERT and UPDATE, run COMMIT to manage data permanently.

COMMIT;

Delete

Usage:

DELETE FROM tableName 
[WHERE conditions]

If you omit the WHERE conditions in the DELETE statement, that will delete all records.

Delete Alison, Bill, and Carol from the Employee table

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