Insert Update Delete
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 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;
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');