4. Inserting, Updating, and Deleting

Inserting a New Record

We want to insert a new record into a table.

-- Single row insert
insert into dept (deptno,dname,loc)
values (50,'PROGRAMMING','BALTIMORE')

-- Multi row insert
insert into dept (deptno,dname,loc)
values (1,'Name1','Loc1'),(2,'Name2','Loc2')

SQL Server, PostgreSQL, and MySQL have the option of inserting multiple rows at a time by including multiple VALUES lists.

Inserting Default Values

A table can be defined to take default values for specific columns. You can insert a row of default values without specifying those values. We can the use of the DEFAULT keyword as a way of explicitly specifying the default value for a column.

create table sometable1 (id integer default 0)
insert into sometable1 values (default)
insert into sometable1 (id) values (default)

create table sometable2 (id integer default 0, foo varchar(10))
insert into sometable2 (name) values ('Bar')

Overriding a Default Value with NULL

We want to override the default value by setting the column to NULL.

create table sometable3 (id integer default 0, foo VARCHAR(10))
insert into sometable3 (id, foo) values (null, 'Brighten')

Copying Rows from One Table into Another

We want to copy rows from one table to another by using a query. For example, we want to copy rows from the DEPT table to the DEPT_EAST table. The DEPT_EAST table has already been created with the same structure (same columns and data types) as DEPT and is currently empty.

insert into dept_east (deptno,dname,loc)
    select deptno,dname,loc
    from dept
    where loc = 'NEW YORK';

Copying a Table Definition

We want to create a new table having the same set of columns definition as an existing table. For example, we want to create a copy of the DEPT table and call it DEPT_2.

-- Oracle, MySQL, and PostgreSQL (just definition)
create table dept_2
as
select *
from dept
where 1 = 0

-- Oracle, MySQL, and PostgreSQL (with data)
create table dept_2
as
select *
from dept

-- SQL Server (just definition)
select *
into dept_2
from dept
where 1 = 0

Inserting into Multiple Tables at Once

We want to take rows returned by a query and insert those rows into multiple target tables. For example, we want to insert rows from DEPT into tables DEPT_EAST, DEPT_WEST, and DEPT_MID. All three tables have the same structure (same columns and data types) as DEPT and are currently empty.

-- Oracle
insert all
    when loc in ('NEW YORK','BOSTON') then
        into dept_east (deptno,dname,loc) values (deptno,dname,loc)
    when loc = 'CHICAGO' then
        into dept_mid (deptno,dname,loc) values (deptno,dname,loc)
    else
        into dept_west (deptno,dname,loc) values (deptno,dname,loc)
    select deptno,dname,loc from dept

In oracle,

INSERT ALL: Inserts data into all specified tables or partitions where the conditions (if any) are met. Every INTO clause is evaluated independently.

INSERT FIRST: Inserts data into the first table or partition where the condition is true. Only the first INTO clause with a true condition is executed for each row.

Blocking Inserts to Certain Columns

We want to prevent users from inserting values into certain table columns. It can be achieve by using View on the table exposing only those columns we want to expose.

create view new_emps as
select empno, ename, job
from emp

Modifying Records in a Table

We want to modify values for some or all rows in a table. It can be achieve using Update.

update emp
set sal = sal*1.10
where deptno = 20

Updating When Corresponding Rows Exist

We want to update rows in one table when corresponding rows exist in another table.

-- Solution 1
update emp
set sal=sal*1.20
where empno in ( select empno from emp_bonus )

-- Solution 2
update emp
set sal = sal*1.20
where exists ( select null from emp_bonus where emp.empno=emp_bonus.empno )

Updating with Values from Another Table

We want to update rows in one table using values from another. One of the most common and straightforward methods is to use a correlated subquery within the UPDATE statement. We can also use a MERGE statement, which is particularly useful for complex operations that involve both inserting and updating rows.

-- MySql
update emp e, new_sal ns
set e.sal=ns.sal,
e.comm=ns.sal/2
where e.deptno=ns.deptno;

-- Oracle (Using a Correlated Subquery) - 1
UPDATE employees e
SET e.dept_name = (SELECT d.dept_name
                   FROM departments d
                   WHERE e.dept_id = d.dept_id);

-- Oracle (Using a Correlated Subquery) - 2
update emp e set (e.sal,e.comm) = (select ns.sal, ns.sal/2
                   from new_sal ns
                   where ns.deptno=e.deptno)
where exists ( select * from new_sal ns where ns.deptno = e.deptno );
                   
-- Oracle (Using Merge)
MERGE INTO employees e
USING departments d
ON (e.dept_id = d.dept_id)
WHEN MATCHED THEN
    UPDATE SET e.dept_name = d.dept_name;

Merging Records

We want to conditionally insert, update, or delete records in a table depending on whether corresponding records exist. If a record exists, then update; if not, then insert; if after updating a row fails to meet a certain condition, delete it.

MySQL does not support MERGE statement.

merge into emp_commission ec
using (select * from emp) emp
    on (ec.empno=emp.empno)
 when matched then
    update set ec.comm = 1000
    delete where (sal < 2000)
 when not matched then
    insert (ec.empno,ec.ename,ec.deptno,ec.comm)
    values (emp.empno,emp.ename,emp.deptno,emp.comm)

Deleting All Records from a Table

We want to delete all the records from a table.

TRUNCATE, which applies to tables and doesn’t use the WHERE clause, is preferred as it is faster. However there is a rollback differences between TRUNCATE and DELETE. TRUNCATE operation cannot be rollback.

delete from emp;

Deleting Specific Records

We want to delete records meeting a specific criterion from a table.

delete from emp where deptno = 10

Deleting a Single Record

We want to delete a single record from a table.

delete from emp where empno = 7782

Deleting Referential Integrity Violations

We want to delete records from a table when those records refer to nonexistent records in some other table. For example, some employees are assigned to departments that do not exist. You want to delete those employees.

delete from emp
where not exists (select * from dept where dept.deptno = emp.deptno)

delete from emp
where deptno not in (select deptno from dept)

Deleting Duplicate Records

We want to delete duplicate records from a table.

delete from dupes
where id not in ( select min(id) from dupes group by name )

-- For MySQL, we cannot reference the same table twice in a delete
delete from dupes
where id not in
(select min(id) from (select id,name from dupes) tmp group by name)

Deleting Records Referenced from Another Table

We want to delete records from one table when those records are referenced from some other table. Consider the emp and dept_accidents table, we want to delete from EMP the records for those employees working at a department that has three or more accidents.

delete from emp
where deptno in ( select deptno from dept_accidents group by deptno having count(*) >= 3 )