Data Modifications in SQL (Insert, Delete & modify)

After a table has been created using the create table command, tuples can be inserted into
the table, or tuples can be deleted or modified.

Insertions - The most simple way to insert a tuple into a table is to use the insert statement
insert into [()]
values ();

For each of the listed columns, a corresponding (matching) value must be specified. Thus an
insertion does not necessarily have to follow the order of the attributes as specified in the create
table statement. If a column is omitted, the value null is inserted instead. If no column list is given, however, for each column as defined in the create table statement a value must be given.

Examples:
insert into PROJECT(PNO, PNAME, PERSONS, BUDGET, PSTART)
values(313, ’DBS’, 4, 150000.42, ’10-OCT-94’);
or
insert into PROJECT
values(313, ’DBS’, 7411, null, 150000.42, ’10-OCT-94’, null);

If there are already some data in other tables, these data can be used for insertions into a new
table. For this, we write a query whose result is a set of tuples to be inserted. Such an insert
statement has the form

insert into [()]
Example: Suppose we have defined the following table:

create table OLDEMP (
ENO number(4) not null,
HDATE date);

We now can use the table EMP to insert tuples into this new relation:

insert into OLDEMP (ENO, HDATE)
select EMPNO, HIREDATE from EMP
where HIREDATE < ’31-DEC-60’;

Updates – For modifying attribute values of (some) tuples in a table, we use the update statement:

update set
= , . . . , =
[where ];

An expression consists of either a constant (new value), an arithmetic or string operation, or
an SQL query. Note that the new value to assign to must a the matching data
type.

An update statement without a where clause results in changing respective attributes of all
tuples in the specified table. Typically, however, only a (small) portion of the table requires an
update.

Examples:
• The employee JONES is transfered to the department 20 as a manager and his salary is
increased by 1000: update EMP set JOB = ’MANAGER’, DEPTNO = 20, SAL = SAL +1000
where ENAME = ’JONES’;

• All employees working in the departments 10 and 30 get a 15% salary increase.
update EMP set SAL = SAL * 1.15 where DEPTNO in (10,30);

Analogous to the insert statement, other tables can be used to retrieve data that are used as
new values. In such a case we have a instead of an .

Example: All salesmen working in the department 20 get the same salary as the manager
who has the lowest salary among all managers.
update EMP set SAL = (select min(SAL) from EMP where JOB = ’MANAGER’) where JOB = ’SALESMAN’ and DEPTNO = 20;

Explanation: The query retrieves the minimum salary of all managers. This value then is
assigned to all salesmen working in department 20.

It is also possible to specify a query that retrieves more than only one value (but still only one
tuple!). In this case the set clause has the form set() = .
It is important that the order of data types and values of the selected row exactly correspond
to the list of columns in the set clause.

Deletions – All or selected tuples can be deleted from a table using the delete command: delete from [where ];

If the where clause is omitted, all tuples are deleted from the table. An alternative command
for deleting all tuples from a table is the truncate table command. However, in this
case, the deletions cannot be undone.

Example: Delete all projects (tuples) that have been finished before the actual date (system date):
delete from PROJECT where PEND < sysdate;
sysdate is a function in SQL that returns the system date. Another important SQL function
is user, which returns the name of the user logged into the current Oracle session.

Commit and Rollback – A sequence of database modifications, i.e., a sequence of insert, update, and delete statements, is called a transaction. Modifications of tuples are temporarily stored in the database system. They become permanent only after the statement commit; has been issued. As long as the user has not issued the commit statement, it is possible to undo all modifications since the last commit. To undo modifications, one has to issue the statement rollback;. It is advisable to complete each modification of the database with a commit (as long as the modification has the expected effect). Note that any data definition command such as create table results in an internal commit. A commit is also implicitly executed when the user
terminates an Oracle session.