Skip to content
Tutorials Center
My learning

Topics

Lesson 7 of 38 ยท SQL in Oracle

INSERT, UPDATE, DELETE and MERGE

Change data in Oracle with INSERT, INSERT ALL, UPDATE, DELETE and MERGE upserts, return changed values, and control transactions with COMMIT and ROLLBACK.

DML, data manipulation language, is how you change rows: INSERT adds them, UPDATE edits them, DELETE removes them, and Oracle's MERGE does an insert-or-update in one statement. Unlike DDL, DML statements do not commit by themselves. They become permanent only when you issue COMMIT, and you can undo them with ROLLBACK until then.

Most examples here run in the editor on a tiny table you create yourself. Oracle-only statements, such as MERGE and INSERT ALL, are shown first, with a runnable stand-in following.

INSERT

SQL
INSERT INTO departments (department_id, name, location)
VALUES (10, 'Engineering', 'Berlin');

Always name the columns. An INSERT without a column list depends on the table's column order, which breaks the day somebody adds a column. In Oracle 23ai you can insert several rows with one VALUES clause:

SQL
INSERT INTO departments (department_id, name, location) VALUES
  (20, 'Design', 'Lisbon'),
  (30, 'Sales', 'Toronto');

Before 23ai, Oracle accepted only one row per VALUES. Older scripts used INSERT ALL:

SQL
INSERT ALL
  INTO departments VALUES (20, 'Design', 'Lisbon')
  INTO departments VALUES (30, 'Sales', 'Toronto')
SELECT * FROM dual;

INSERT ALL can also load several different tables from one SELECT, and with WHEN conditions it routes rows between them. It is a favorite for data-loading scripts.

INSERT ... SELECT

Copy rows from a query:

SQL INSERT ... SELECT

Press Run (or Ctrl + Enter) to see the result here.

UPDATE

UPDATE changes columns in every row that matches WHERE. Without a WHERE it changes every row.

SQL
UPDATE employees
SET salary = salary * 1.10,
    last_raise = SYSDATE
WHERE department_id = 10;

You can set a column from a subquery, or set several columns at once from one subquery:

SQL
UPDATE employees e
SET (salary, department_id) =
    (SELECT max_salary, department_id FROM job_limits j WHERE j.job_id = e.job_id)
WHERE EXISTS (SELECT 1 FROM job_limits j WHERE j.job_id = e.job_id);
SQL UPDATE with a correlated subquery

Press Run (or Ctrl + Enter) to see the result here.

DELETE

SQL
DELETE FROM employees WHERE department_id = 30;
DELETE employees WHERE hire_date < DATE '2010-01-01';   -- FROM is optional in Oracle

DELETE removes rows one by one and can be rolled back. To empty a big table quickly, TRUNCATE is much faster, but it cannot be rolled back.

MERGE: update or insert in one statement

MERGE compares a source (a table or query) with a target table. Rows that match are updated, rows that do not match are inserted. It is the standard Oracle tool for loading changes from a staging table.

SQL
MERGE INTO employees t
USING new_hires s
   ON (t.employee_id = s.employee_id)
WHEN MATCHED THEN
  UPDATE SET t.salary = s.salary,
             t.department_id = s.department_id
WHEN NOT MATCHED THEN
  INSERT (employee_id, last_name, salary, department_id)
  VALUES (s.employee_id, s.last_name, s.salary, s.department_id);

Rules to remember: the ON columns cannot be updated in the WHEN MATCHED branch, each target row may be touched by only one source row (otherwise ORA-30926: unable to get a stable set of rows), and you can add DELETE WHERE inside the matched branch to remove rows after updating them.

The upsert stand-in uses SQLite's ON CONFLICT, which does the same job when the match is on a primary key:

SQL Stand-in for MERGE (upsert)

Press Run (or Ctrl + Enter) to see the result here.

Bo's salary is updated to 5000 (matched) and Cy is inserted (not matched).

RETURNING: read what you changed

In PL/SQL, RETURNING ... INTO hands back values from the row you just changed, so you avoid a second query:

PLSQL
DECLARE
  v_id employees.employee_id%TYPE;
BEGIN
  INSERT INTO employees (last_name, email)
  VALUES ('Okafor', '[email protected]')
  RETURNING employee_id INTO v_id;
  DBMS_OUTPUT.PUT_LINE('New id: ' || v_id);
END;
/

In 23ai, plain SQL RETURNING also supports OLD and NEW references on UPDATE, so you can see both the before and after values.

Transactions: COMMIT, ROLLBACK, SAVEPOINT

A transaction begins with your first DML statement. Nobody else sees your uncommitted changes.

SQL
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;               -- make both changes permanent together
SQL
SAVEPOINT before_bonus;
UPDATE employees SET salary = salary * 2;   -- oops
ROLLBACK TO before_bonus;                   -- undo back to the savepoint
ROLLBACK;                                   -- or undo everything since the last commit

Closing SQL*Plus normally commits pending work; a crash or ROLLBACK does not. Chapter 8 returns to transactions in depth.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What happens to uncommitted DML when you issue ROLLBACK?

    ROLLBACK discards every DML change since the last COMMIT (or back to a savepoint with ROLLBACK TO).
  2. 2. What does an UPDATE without a WHERE clause do?

    Without a filter, UPDATE touches all rows. Run a SELECT with the same WHERE first when unsure.
  3. 3. When does the WHEN NOT MATCHED branch of a MERGE run?

    The default NOT MATCHED branch handles source rows that have no match in the target, and inserts them.
  4. 4. Which statement is not rolled back by ROLLBACK?

    TRUNCATE is DDL and commits automatically.

Summary

  • INSERT adds rows (multi-row VALUES works in 23ai; INSERT ALL in all versions); always list columns.
  • UPDATE and DELETE need a WHERE or they affect every row.
  • MERGE upserts from a source into a target; the stand-in here is INSERT ... ON CONFLICT.
  • RETURNING INTO captures changed values without a second query.
  • DML is transactional: COMMIT saves, ROLLBACK undoes, SAVEPOINT marks a point to return to.

Next: Sequences and Identity Columns