DML – Data Manipulation Language: INSERT, UPDATE, DELETE, MERGE

Subject: Information Technology – Database Systems School level: 11th grade – HTL Informatik Requirements: DQL – SELECT, joins, subqueries, SCOTT schema Author: HTL Pinkafeld – IF/IT

1. Overview of DML and transactions

1.1 What is DML?

DML (Data Manipulation Language) includes all SQL statements that manipulate data in tables:

DML-Anweisungen:
├── INSERT  → neue Zeilen hinzufügen
├── UPDATE  → bestehende Zeilen ändern
├── DELETE  → Zeilen entfernen
└── MERGE   → einfügen ODER aktualisieren (Upsert)

Unlike DDL (CREATE, ALTER, DROP), DML works on the data - not the structure.

1.2 DML and Transactions

Each DML statement automatically starts a transaction (if none is already active). Changes are initially only visible for your own session and are only saved permanently by COMMIT or undone by ROLLBACK:

DML-Anweisung
      │
      ▼
Change in the buffer (only visible for your own session)
      │
├── COMMIT → saved permanently, visible to everyone
└── ROLLBACK → all changes since the last COMMIT are rolled back

Important in Oracle: DDL statements (CREATE, ALTER, DROP) always execute an implicit COMMIT - all open DML changes are saved permanently!

2. INSERT – insert rows

2.1 INSERT with column list (recommended)

-- Complete syntax with column list
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES         (8000, 'MUSTER', 'CLERK', 7782,
                TO_DATE('15.09.2024', 'DD.MM.YYYY'),
                1500, NULL, 10);

-- Fill only certain columns (remaining ones get NULL or DEFAULT)
INSERT INTO EMP (EMPNO, ENAME, JOB, SAL, DEPTNO)
VALUES         (8001, 'HUBER', 'ANALYST', 3200, 20);
-- MGR, HIREDATE, COMM erhalten NULL

2.2 INSERT without column list

-- Without column list: Order must correspond exactly to the table definition
-- All columns must be specified!
INSERT INTO DEPT
VALUES (50, 'IT', 'WIEN');

Recommendation: Always specify the column list. Without a column list, the statement breaks as soon as someone adds a column to the table.

2.3 Different value types

-- Zahlen: direkt angeben
INSERT INTO EMP (EMPNO, SAL) VALUES (8002, 2500);

-- Strings: single quotes
INSERT INTO EMP (EMPNO, ENAME) VALUES (8003, 'SCHULZ');

-- Date: TO_DATE or date literal
INSERT INTO EMP (EMPNO, HIREDATE)
VALUES (8004, TO_DATE('01.01.2025', 'DD.MM.YYYY'));

-- Insert NULL explicitly
INSERT INTO EMP (EMPNO, ENAME, COMM)
VALUES (8005, 'NOVAK', NULL);

-- SYSDATE for current date
INSERT INTO EMP (EMPNO, ENAME, HIREDATE)
VALUES (8006, 'WAGNER', SYSDATE);

2.4 INSERT with DEFAULT

-- Wenn eine Spalte einen DEFAULT-Wert hat, kann DEFAULT verwendet werden
CREATE TABLE PROTOKOLL (
    ID        NUMBER         PRIMARY KEY,
    AKTION    VARCHAR2(100),
    ZEITSTEMPEL DATE          DEFAULT SYSDATE,
    BENUTZER  VARCHAR2(30)   DEFAULT USER
);

INSERT INTO PROTOKOLL (ID, AKTION)
VALUES (1, 'Login');
-- ZEITSTEMPEL und BENUTZER erhalten ihre DEFAULT-Werte

2.5 Multiple lines at the same time (INSERT ALL)

-- INSERT ALL: multiple lines in one statement
INSERT ALL
    INTO DEPT VALUES (50, 'IT',        'WIEN')
    INTO DEPT VALUES (60, 'MARKETING', 'GRAZ')
    INTO DEPT VALUES (70, 'SUPPORT',   'LINZ')
SELECT * FROM DUAL;   -- Pflicht: SELECT-Klausel am Ende

3. INSERT with subquery

3.1 Copy data from another table

-- Create backup table
CREATE TABLE EMP_BACKUP AS SELECT * FROM EMP WHERE 1=2;
-- (empty table with the same structure – 1=2 is always wrong)

-- Daten hineinkopieren
INSERT INTO EMP_BACKUP
SELECT * FROM EMP;

-- Only certain employees copy
INSERT INTO EMP_BACKUP (EMPNO, ENAME, JOB, SAL, DEPTNO)
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO
FROM   EMP
WHERE  DEPTNO = 20;

3.2 INSERT with calculated values

-- Fill new table for annual salaries
CREATE TABLE JAHRESGEHALT (
    EMPNO       NUMBER(4),
    ENAME       VARCHAR2(10),
    JAHRESBETRAG NUMBER(10,2)
);

INSERT INTO JAHRESGEHALT (EMPNO, ENAME, JAHRESBETRAG)
SELECT EMPNO,
       ENAME,
       SAL * 12 + NVL(COMM, 0) * 12
FROM   EMP;

COMMIT;

3.3 INSERT ALL with conditions

-- Insert employees into different tables depending on their salary
INSERT ALL
    WHEN SAL < 1500 THEN
        INTO EMP_NIEDRIG VALUES (EMPNO, ENAME, SAL)
    WHEN SAL BETWEEN 1500 AND 3000 THEN
        INTO EMP_MITTEL VALUES (EMPNO, ENAME, SAL)
    WHEN SAL > 3000 THEN
        INTO EMP_HOCH VALUES (EMPNO, ENAME, SAL)
SELECT EMPNO, ENAME, SAL FROM EMP;

4. UPDATE – Update rows

4.1 Basic syntax

UPDATE tabellenname
SET    spalte1 = wert1,
       spalte2 = wert2,
       ...
WHERE  bedingung;

⚠️ Warning: An UPDATE without WHERE updates all rows of the table!

4.2 Simple UPDATE examples

-- Increase the salary of a specific employee
UPDATE EMP
SET    SAL = 3500
WHERE  EMPNO = 7369;

-- Change multiple columns at once
UPDATE EMP
SET    SAL     = 2000,
       JOB     = 'ANALYST',
       DEPTNO  = 20
WHERE  EMPNO = 7900;

-- Change relative to current value (10% salary increase for department 20)
UPDATE EMP
SET    SAL = SAL * 1.1
WHERE  DEPTNO = 20;

-- All salesmen receive a commission of 10% of their salary (if none already)
UPDATE EMP
SET    COMM = SAL * 0.1
WHERE  JOB = 'SALESMAN'
  AND  COMM IS NULL;

4.3 UPDATE multiple columns with expressions

-- Update date and profession at the same time
UPDATE EMP
SET    HIREDATE = SYSDATE,
       JOB      = 'MANAGER',
       MGR      = 7839
WHERE  ENAME = 'MUSTER';

-- Calculated value: Round salary to nearest 100
UPDATE EMP
SET    SAL = CEIL(SAL / 100) * 100
WHERE  DEPTNO = 30;

4.4 UPDATE without WHERE – All lines

-- All employees: Adjust salaries to compensate for inflation
UPDATE EMP
SET    SAL = ROUND(SAL * 1.03, 2);

-- Check before COMMIT:
SELECT EMPNO, ENAME, SAL FROM EMP ORDER BY EMPNO;

COMMIT;

5. UPDATE with subquery

5.1 Take value from another table

-- Place SCOTT's department at the DALLAS location
UPDATE EMP
SET    DEPTNO = (SELECT DEPTNO
                 FROM   DEPT
                 WHERE  LOC = 'DALLAS')
WHERE  ENAME = 'SCOTT';

-- Set salary to the average for your own department
-- (korrelierte Subquery im SET!)
UPDATE EMP e
SET    SAL = (SELECT ROUND(AVG(SAL), 0)
              FROM   EMP i
              WHERE  i.DEPTNO = e.DEPTNO)
WHERE  JOB = 'CLERK';

5.2 Multiple columns with subquery

-- Set multiple columns from a subquery at the same time
UPDATE EMP
SET    (SAL, COMM) = (SELECT SAL * 1.1, COMM * 1.2
                      FROM   EMP
                      WHERE  ENAME = 'ALLEN')
WHERE  ENAME = 'WARD';

-- Alternativ mit einzelnen Subqueries
UPDATE EMP
SET    SAL  = (SELECT SAL  * 1.1 FROM EMP WHERE ENAME = 'ALLEN'),
       COMM = (SELECT COMM * 1.2 FROM EMP WHERE ENAME = 'ALLEN')
WHERE  ENAME = 'WARD';

5.3 UPDATE with EXISTS

-- Only update employees whose department is in CHICAGO
UPDATE EMP e
SET    SAL = SAL * 1.05
WHERE  EXISTS (SELECT 1
               FROM   DEPT d
               WHERE  d.DEPTNO = e.DEPTNO
                 AND  d.LOC = 'CHICAGO');

6. DELETE – delete lines

6.1 Basic syntax

DELETE FROM tabellenname
WHERE  bedingung;

⚠️ Warning: A DELETE without WHERE deletes all rows of the table! The structure remains intact.

6.2 Simple DELETE examples

-- Delete a specific employee
DELETE FROM EMP
WHERE  EMPNO = 8000;

-- Delete all employees in a department
DELETE FROM EMP
WHERE  DEPTNO = 40;

-- Delete employees who were hired before 1982
DELETE FROM EMP
WHERE  HIREDATE < TO_DATE('01.01.1982', 'DD.MM.YYYY');

-- Delete all employees without commission (COMM is NULL)
DELETE FROM EMP
WHERE  COMM IS NULL;

6.3 DELETE with subquery

-- Delete all employees in departments that are in NEW YORK
DELETE FROM EMP
WHERE  DEPTNO IN (SELECT DEPTNO
                  FROM   DEPT
                  WHERE  LOC = 'NEW YORK');

-- Delete employees whose salary is below average
DELETE FROM EMP
WHERE  SAL < (SELECT AVG(SAL) FROM EMP);

-- With EXISTS: Delete employees who are not assigned to a department
DELETE FROM EMP e
WHERE  NOT EXISTS (SELECT 1
                   FROM   DEPT d
                   WHERE  d.DEPTNO = e.DEPTNO);

6.4 Sequence of deletion with foreign keys

-- ERROR: Cannot delete department while employees are referencing it
DELETE FROM DEPT WHERE DEPTNO = 20;
-- ORA-02292: integrity constraint violated - child record found

-- CORRECT: First delete employees, then department
DELETE FROM EMP  WHERE DEPTNO = 20;
DELETE FROM DEPT WHERE DEPTNO = 20;
COMMIT;

7. TRUNCATE – Empty table

7.1 TRUNCATE vs. DELETE

-- Remove all rows from a table
TRUNCATE TABLE EMP_BACKUP;

7.2 When to use TRUNCATE?

-- Good use cases for TRUNCATE:
-- 1. Flush staging tables before reloading
TRUNCATE TABLE STAGE_IMPORT;

-- 2. Remove test data before refilling
TRUNCATE TABLE TEST_DATEN;

-- ATTENTION: TRUNCATE cannot be undone!
-- Check before: SELECT COUNT(*) FROM table;

8. Transaction Control – COMMIT and ROLLBACK

8.1 COMMIT – save changes permanently

-- Ablauf einer typischen Transaktion:

-- 1. Make changes
INSERT INTO EMP (EMPNO, ENAME, JOB, SAL, DEPTNO)
VALUES         (8010, 'BAUER', 'CLERK', 1400, 30);

UPDATE EMP SET SAL = 5500 WHERE EMPNO = 7839;

-- 2. Check result
SELECT EMPNO, ENAME, SAL FROM EMP WHERE EMPNO IN (8010, 7839);

-- 3. Save permanently
COMMIT;
-- Now visible to all other users and can no longer be undone

8.2 ROLLBACK – Undo changes

-- make changes
DELETE FROM EMP WHERE DEPTNO = 20;

-- Oops! Wrong command
-- Check:
SELECT COUNT(*) FROM EMP;   -- 9 instead of 14

-- Undo (as long as no COMMIT!)
ROLLBACK;

-- Check:
SELECT COUNT(*) FROM EMP;   -- back to 14

8.3 Automatic COMMIT and ROLLBACK

-- Implizites COMMIT tritt auf bei:
-- 1. DDL-Anweisung (CREATE, ALTER, DROP, TRUNCATE)
-- 2. Normales Beenden der Session (EXIT in SQL*Plus)

-- Implizites ROLLBACK tritt auf bei:
-- 1. Absturz / Verbindungsabbruch
-- 2. Session termination without COMMIT (in some tools)

8.4 Read Consistency

-- Oracle Multiversion Concurrency Control (MVCC):
-- Andere Benutzer sehen IMMER den letzten COMMIT-Stand
-- Your own session sees your uncommitted changes

-- Session A:                    Session B:
UPDATE EMP SET SAL=999            SELECT SAL FROM EMP
WHERE EMPNO=7839;                 WHERE EMPNO=7839;
                                  -- Sieht noch 5000 (vor Update)
COMMIT;
                                  SELECT SAL FROM EMP
                                  WHERE EMPNO=7839;
                                  -- Sieht jetzt 999

9. SAVEPOINT – partial withdrawal

9.1 Set savepoints

With SAVEPOINT intermediate points can be set within a transaction. A ROLLBACK TO savepoint only undoes the changes since the savepoint:

-- Transaktion mit mehreren Schritten

-- Step 1: New department
INSERT INTO DEPT VALUES (50, 'ENTWICKLUNG', 'WIEN');
SAVEPOINT nach_dept;

-- Step 2: Add employees
INSERT INTO EMP (EMPNO, ENAME, JOB, SAL, DEPTNO)
VALUES (8020, 'KLEIN', 'ANALYST', 3000, 50);
SAVEPOINT nach_emp1;

INSERT INTO EMP (EMPNO, ENAME, JOB, SAL, DEPTNO)
VALUES (8021, 'GROSS', 'CLERK', 1200, 50);
SAVEPOINT nach_emp2;

-- Kontrollabfrage
SELECT * FROM EMP WHERE DEPTNO = 50;

-- Just undo last INSERT
ROLLBACK TO nach_emp1;
-- emp2 (LARGE) has been deleted, emp1 (SMALL) and DEPT 50 remain

-- Check
SELECT * FROM EMP WHERE DEPTNO = 50;   -- just SMALL

-- Confirm everything
COMMIT;

9.2 Savepoint hierarchy

COMMIT / Start
    │
    ├── DML 1
    ├── SAVEPOINT A
    ├── DML 2
    ├── SAVEPOINT B
    ├── DML 3
    └── SAVEPOINT C

ROLLBACK TO B → DML 3 and SAVEPOINT C are undone
ROLLBACK TO A → DML 2, DML 3, SAVEPOINT B and C undone
ROLLBACK → Undo everything since the last COMMIT
COMMIT → Save everything permanently (savepoints deleted)

10. MERGE – Upsert

10.1 What is MERGE?

MERGE combines INSERT and UPDATE in a single statement: if a row exists in the target table, it is updated - if not, it is inserted. This pattern is also called Upsert (Update + Insert).

10.2 Basic syntax

MERGE INTO zieltabelle z
USING quelltabelle   q ON (z.schluessel = q.schluessel)
WHEN MATCHED THEN
    UPDATE SET z.spalte1 = q.spalte1,
               z.spalte2 = q.spalte2
WHEN NOT MATCHED THEN
    INSERT (spalte1, spalte2, ...)
    VALUES (q.spalte1, q.spalte2, ...);

10.3 Practical example

-- Staging table with new/updated employee data
CREATE TABLE EMP_UPDATES (
    EMPNO   NUMBER(4),
    ENAME   VARCHAR2(10),
    SAL     NUMBER(7,2),
    DEPTNO  NUMBER(2)
);

-- Neue Daten simulieren:
-- 7369 = existing (SAL changed), 9001 = new
INSERT INTO EMP_UPDATES VALUES (7369, 'SMITH',  999, 20);
INSERT INTO EMP_UPDATES VALUES (9001, 'NEUMANN', 2500, 30);

-- MERGE: update or insert
MERGE INTO EMP z
USING EMP_UPDATES q ON (z.EMPNO = q.EMPNO)
WHEN MATCHED THEN
    UPDATE SET z.SAL    = q.SAL,
               z.DEPTNO = q.DEPTNO
WHEN NOT MATCHED THEN
    INSERT (EMPNO, ENAME, SAL, DEPTNO)
    VALUES (q.EMPNO, q.ENAME, q.SAL, q.DEPTNO);

-- Check result:
SELECT * FROM EMP WHERE EMPNO IN (7369, 9001);
-- 7369/SMITH hat jetzt SAL=999
-- 9001/NEUMANN has been added

COMMIT;

10.4 MERGE with DELETE

-- MERGE can also delete rows (Oracle extension)
MERGE INTO EMP z
USING EMP_UPDATES q ON (z.EMPNO = q.EMPNO)
WHEN MATCHED THEN
    UPDATE SET z.SAL = q.SAL
    DELETE WHERE z.SAL < 1000   -- Delete line after update if SAL < 1000
WHEN NOT MATCHED THEN
    INSERT (EMPNO, ENAME, SAL, DEPTNO)
    VALUES (q.EMPNO, q.ENAME, q.SAL, q.DEPTNO);

11. Constraints and DML errors

11.1 Common Constraint Errors

-- ORA-00001: UNIQUE / PRIMARY KEY verletzt
INSERT INTO DEPT VALUES (10, 'TEST', 'WIEN');
-- DEPTNO 10 existiert bereits!

-- ORA-02291: Foreign key – parent key not found
INSERT INTO EMP (EMPNO, ENAME, DEPTNO) VALUES (9999, 'TEST', 99);
-- Department 99 does not exist!

-- ORA-02292: Foreign key – child record exists
DELETE FROM DEPT WHERE DEPTNO = 20;
-- Still employees in department 20!

-- ORA-01400: NOT NULL verletzt
INSERT INTO EMP (EMPNO, ENAME) VALUES (NULL, 'TEST');
-- EMPNO ist NOT NULL!

-- ORA-02290: CHECK Constraint verletzt
-- (wenn z.B. CHECK (SAL > 0) definiert)
UPDATE EMP SET SAL = -100 WHERE EMPNO = 7369;

11.2 Catch and analyze errors

-- Show error number and error message in SQL*Plus
-- Outputs automatically after an error

-- Important ORA error codes for DML:
-- ORA-00001  Unique constraint violated
-- ORA-01400  Cannot insert NULL
-- ORA-02290  Check constraint violated
-- ORA-02291  Parent key not found (FK-Insert)
-- ORA-02292  Child record found (FK-Delete)
-- ORA-01427  Single-row subquery returns more than one row
-- ORA-01722  Invalid number (wrong type conversion)
-- ORA-01843 Not a valid month (incorrect date format)

11.3 DML Safety Net: Check before COMMIT

-- Good practice: Check the changes before each COMMIT

-- 1. Make changes
UPDATE EMP SET SAL = SAL * 1.15 WHERE DEPTNO = 30;

-- 2. Check number of affected rows (SQL%ROWCOUNT in PL/SQL)
-- In SQL Developer: Statuszeile zeigt "6 rows updated"

-- 3. Check data
SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO = 30;

-- 4. Only then commit or uncommit
COMMIT;    -- or ROLLBACK;

12. Summary and outlook

Overview of DML statements

-- INSERT: insert new line
INSERT INTO tabelle (col1, col2) VALUES (val1, val2);
INSERT INTO tabelle (col1, col2) SELECT col1, col2 FROM quelle;

-- UPDATE: change existing lines
UPDATE tabelle SET col1 = val1, col2 = val2 WHERE bedingung;
UPDATE tabelle SET col1 = (SELECT ... FROM ...) WHERE bedingung;

-- DELETE: Remove lines
DELETE FROM tabelle WHERE bedingung;
DELETE FROM tabelle WHERE col IN (SELECT ... FROM ...);

-- TRUNCATE: remove all lines (DDL, no ROLLBACK!)
TRUNCATE TABLE tabelle;

-- MERGE: insert or update
MERGE INTO ziel z
USING quelle q ON (z.id = q.id)
WHEN MATCHED     THEN UPDATE SET z.col = q.col
WHEN NOT MATCHED THEN INSERT (id, col) VALUES (q.id, q.col);

-- Transaction control
COMMIT;                   -- save permanently
ROLLBACK;                 -- undo everything since the last COMMIT
SAVEPOINT name;           -- set a savepoint
ROLLBACK TO name;         -- back to the save point

Golden Rules for DML

Immer WHERE bei UPDATE und DELETE prüfen
  → SELECT mit gleicher WHERE-Bedingung vorher ausführen!

Vor COMMIT Ergebnis kontrollieren
→ Check number of affected lines and content

ROLLBACK is only possible before the next COMMIT
  → Kein ROLLBACK nach DDL-Anweisung!

Pay attention to the order of FK dependencies
→ Delete child tables first, then parent tables

TRUNCATE ist unwiderruflich
→ Always COUNT(*) in front of it, check backup beforehand

Bei NOT IN mit Subquery NULL ausschließen
  → WHERE spalte NOT IN (SELECT ... WHERE ... IS NOT NULL)

Checklist

INSERT mit und ohne Spaltenliste
INSERT mit Subquery (Daten kopieren)
UPDATE mit WHERE und Subquery
DELETE mit WHERE und Fremdschlüssel-Reihenfolge
TRUNCATE vs. DELETE kennen
COMMIT und ROLLBACK anwenden
SAVEPOINT setzen und ROLLBACK TO verwenden
MERGE: Syntax und Anwendungsfall erklären
Constraint-Fehlermeldungen lesen und beheben
DML safety net: check before COMMIT

Typical exam tasks

-- 1. Insert new department and employees
INSERT INTO DEPT VALUES (50, 'ENTWICKLUNG', 'WIEN');
INSERT INTO EMP (EMPNO, ENAME, JOB, SAL, DEPTNO, HIREDATE)
VALUES (9000, 'TESTER', 'ANALYST', 3500, 50, SYSDATE);
COMMIT;

-- 2. All clerks in department 20 receive 15% more salary
UPDATE EMP SET SAL = ROUND(SAL * 1.15, 0)
WHERE  JOB = 'CLERK' AND DEPTNO = 20;
SELECT ENAME, SAL FROM EMP WHERE JOB = 'CLERK' AND DEPTNO = 20;
COMMIT;

-- 3. Delete all employees whose salary is below average
DELETE FROM EMP
WHERE SAL < (SELECT AVG(SAL) FROM EMP);
ROLLBACK;   -- Undo for further exercises

-- 4. Set salary to department average (correlated subquery)
UPDATE EMP e
SET    SAL = (SELECT ROUND(AVG(SAL), 0) FROM EMP i WHERE i.DEPTNO = e.DEPTNO)
WHERE  JOB = 'CLERK';
SELECT ENAME, SAL, DEPTNO FROM EMP WHERE JOB = 'CLERK';
ROLLBACK;

-- 5. MERGE: Gehaltstabelle mit aktuellen Werten synchronisieren
MERGE INTO JAHRESGEHALT j
USING (SELECT EMPNO, ENAME, SAL*12 + NVL(COMM,0)*12 AS jahresbetrag
       FROM EMP) q
ON (j.EMPNO = q.EMPNO)
WHEN MATCHED THEN
    UPDATE SET j.JAHRESBETRAG = q.jahresbetrag
WHEN NOT MATCHED THEN
    INSERT (EMPNO, ENAME, JAHRESBETRAG)
    VALUES (q.EMPNO, q.ENAME, q.jahresbetrag);
COMMIT;

Outlook: Next topics

• DDL: CREATE TABLE, ALTER TABLE, define constraints, DROP
• Views: Create and use virtual tables
• Sequences and Synonyms
• PL/SQL: Procedural extension, procedures, functions, triggers

HTL Pinkafeld – IF/IT | Oracle SQL | 11th grade