Managing Tables with DML Statements: Insert, Update, Delete, Merge

DML is Data Manipulation Language. It changes the data inside tables — INSERT, UPDATE, DELETE, and MERGE. DDL is Data Definition Language. It changes the structure of tables and other database objects — CREATE, ALTER, DROP, TRUNCATE.

-- DML: changes the rows
INSERT INTO customers (customer_id, customer_name) VALUES ('C008', 'Henry Co');
UPDATE customers SET credit_limit = 60000 WHERE customer_id = 'C001';
DELETE FROM orders WHERE order_status = 'Pending';

-- DDL: changes the structure
CREATE TABLE archived_orders (order_id NUMBER, customer_id VARCHAR2(10));
ALTER TABLE customers ADD (contact_email VARCHAR2(100));
DROP TABLE archived_orders;

The practical difference is reversal. DML changes live inside a transaction — you can ROLLBACK and undo them until you COMMIT. DDL changes are auto-committed by default and cannot be undone. That is why production DML runs in explicit transactions and DDL runs during maintenance windows with backups as the safety net.

INSERT INTO adds new rows to a table. There are two ways to write it, and one of them is dramatically safer than the other.

-- Explicit columns (recommended)
INSERT INTO customers (customer_id, customer_name, region, credit_limit)
VALUES ('C008', 'Henry Co', 'East', 45000);

-- Positional (avoid in production)
INSERT INTO customers
VALUES ('C009', 'Ivan Trading', 'East', 32000);

The first form lists the columns explicitly. The second relies on physical column order in the table. Both work today. Only the first one keeps working tomorrow when someone adds a new column in the middle of the schema.

The positional form fails silently in that scenario — the values shift by one position, and Ivan Trading's region ends up stored in whatever column happens to be where the email should be. The insert succeeds, the data is wrong, and nothing tells you.

List the columns every time. It costs a few seconds and prevents an entire class of bugs that only show up during schema migrations.

You can insert several rows in one statement by providing multiple value tuples. Every major database supports this. Oracle also has a variant called INSERT ALL.

-- Portable multi-row insert
INSERT INTO customers (customer_id, customer_name, region, credit_limit)
VALUES
    ('C008', 'Henry Co',       'East', 45000),
    ('C009', 'Ivan Trading',   'East', 32000),
    ('C010', 'Julia Wholesale','West', 60000);

One statement, three rows. The database treats the whole thing as a single transaction — either all three rows are inserted or none of them are.

Oracle's alternative uses INSERT ALL with a series of INTO clauses:

INSERT ALL
    INTO customers (customer_id, customer_name, region, credit_limit)
         VALUES ('C008', 'Henry Co', 'East', 45000)
    INTO customers (customer_id, customer_name, region, credit_limit)
         VALUES ('C009', 'Ivan Trading', 'East', 32000)
SELECT * FROM dual;

Both forms are faster than three separate single-row INSERT statements because they involve one round trip to the database instead of three. On real systems, batching regularly cuts load times by ten times or more.

When the rows you want already exist somewhere in the database, you use INSERT INTO ... SELECT ... to fetch and copy them in one statement.

INSERT INTO high_value_customers (customer_id, customer_name, total_spend)
SELECT c.customer_id,
       c.customer_name,
       SUM(o.order_amount)
FROM   customers c
JOIN   orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
HAVING SUM(o.order_amount) > 15000;

Every customer whose total order value exceeds 15,000 gets copied into the summary table. On our data, that is Alice Corp (28,500), Charlie Ltd (22,000), and Diana Retail (18,000). Bob Traders, with a total of 7,700, does not qualify.

The WHERE clause or HAVING clause inside the SELECT controls which rows get copied. This is how you move filtered data between tables without writing per-row logic in application code.

The target columns and source columns must be compatible — same count, compatible types. The names do not need to match. If the target table has columns named id, name, and amount, you can still copy from a source with customer_id, customer_name, and order_total — the values are matched by position, not by name.

UPDATE modifies existing rows. The syntax names the table, lists the columns to change in the SET clause, and (usually) filters the rows in a WHERE clause.

UPDATE customers
SET    credit_limit = credit_limit * 1.1
WHERE  region = 'North';

Every North-region customer gets a 10% bump. On our data, Alice Corp (50,000 → 55,000), Charlie Ltd (75,000 → 82,500), and George Wholesale (80,000 → 88,000) are affected. Everyone else is untouched.

Forget the WHERE clause and every row gets the same new value:

UPDATE customers SET credit_limit = 10000;

All seven customers now have a 10,000 credit limit. Fiona Mart's NULL becomes 10,000. George Wholesale's 80,000 becomes 10,000. There is no undo once the transaction commits.

The professional habit that prevents this: run the equivalent SELECT first. SELECT COUNT(*) FROM customers WHERE region = 'North' tells you how many rows the UPDATE will affect. If the number matches expectation, proceed. If not, investigate.

Two patterns. The first applies the same change to every row that matches a condition. The second applies different changes to different rows, using a CASE expression.

-- Same change for a group
UPDATE customers
SET    credit_limit = credit_limit + 5000
WHERE  region = 'South';

Every South-region customer gets a 5,000 increase. On our data, Bob Traders (25,000 → 30,000) and Ethan Supplies (55,000 → 60,000). Everyone else is untouched.

-- Different changes for different groups
UPDATE customers
SET    credit_limit = CASE
           WHEN region = 'North' THEN credit_limit * 1.10
           WHEN region = 'South' THEN credit_limit * 1.05
           WHEN region = 'West'  THEN credit_limit * 1.02
           ELSE credit_limit
       END;

One statement, three rules. North customers get 10%, South get 5%, West get 2%. On our data, Alice Corp (50,000 → 55,000), Bob Traders (25,000 → 26,250), Diana Retail (40,000 → 40,800). Fiona Mart's NULL is not affected because NULL * 1.02 is NULL.

The CASE version has one big advantage over three separate UPDATE statements: it runs as a single atomic operation. Either all three updates apply or none do. The three-statement version can fail halfway, leaving the data in a partial state that is hard to recover from.

The WHERE clause is what limits an UPDATE to specific rows. Without it, every row in the table gets updated.

UPDATE customers
SET    credit_limit = credit_limit + 5000
WHERE  region = 'North'
  AND  credit_limit < 60000;

Two conditions combined with AND. Only North-region customers with a current credit limit below 60,000 get the increase. On our data, that is just Alice Corp (50,000 → 55,000). Charlie Ltd (75,000) and George Wholesale (80,000) are both above the threshold and are left alone.

Any condition that works in a regular SELECT works in the WHERE clause of an UPDATE. Comparisons, IN, BETWEEN, LIKE, IS NULL, subqueries — all valid.

The habit worth building: whatever filter you write in the WHERE clause, run it as a SELECT first. The count of rows the SELECT returns is the count of rows the UPDATE will affect. Getting that number in front of you before the update runs is the difference between confidence and a phone call at 3 AM.

DELETE FROM removes rows that match a condition. Without a condition, every row is removed.

-- Filtered delete
DELETE FROM orders
WHERE  order_status = 'Pending';

On our data, only order 1004 (Charlie Ltd's 22,000 pending order) is removed. The rest of the table is untouched.

-- Unfiltered delete
DELETE FROM orders;

Every row is removed. The table structure remains — columns, indexes, constraints all still exist. The table is empty but ready for new data.

The mistake to avoid: running DELETE FROM tablename when you meant DELETE FROM tablename WHERE .... The first form removes everything. If the transaction has not committed, ROLLBACK saves you. If it has, the data is gone.

The habit worth building: before any DELETE, run the equivalent SELECT COUNT(*) and confirm the count. Then run the DELETE and check the rows-affected count the database reports. If it says zero when you expected five, the WHERE clause matched nothing. If it says a million when you expected five, you have just deleted your production data.

Two options. DELETE FROM tablename removes every row but keeps the table, indexes, and constraints. TRUNCATE TABLE tablename does the same thing much faster, with some caveats.

-- Option 1: DELETE (slow on large tables)
DELETE FROM orders;

-- Option 2: TRUNCATE (fast, all-or-nothing)
TRUNCATE TABLE orders;

DELETE logs each row it removes, so it can be rolled back before the commit. On a table with millions of rows, that logging adds up to a lot of time.

TRUNCATE removes all rows in a single operation without logging them individually. It is dramatically faster on large tables. It also resets any auto-increment counter, so the next inserted row starts from the beginning of the sequence.

The choice comes down to three questions. Do you need to filter? (Only DELETE can.) Do you need to roll back? (DELETE, yes. TRUNCATE, usually yes inside a transaction but varies by database.) Does the table have a foreign key pointing at it? (TRUNCATE fails; DELETE respects the constraint.)

For clearing a staging table or wiping temporary data, TRUNCATE is almost always the right choice. For anything else, DELETE gives you more control.

The WHERE clause in a DELETE works exactly like it does in a SELECT — it filters which rows the statement acts on.

DELETE FROM customers
WHERE  region = 'West'
  AND  credit_limit IS NULL;

That removes West-region customers whose credit limit is NULL. On our data, that is only Fiona Mart. Diana Retail is also in the West but has a credit limit, so she stays.

Multiple conditions combine with AND, OR, and NOT. Subqueries work too:

DELETE FROM customers
WHERE  customer_id IN (
    SELECT customer_id FROM orders WHERE order_amount > 20000
);

That removes customers who have placed a very large order. On our data, only Charlie Ltd — his order 1004 is 22,000.

The pattern to remember: the WHERE clause in a DELETE is where all your targeting lives. Every condition you would write in a SELECT's WHERE clause works here. The only difference is that the matching rows get removed instead of returned.

INSERT ALL is Oracle's batch insert syntax. It lets you insert multiple rows — into the same table or different tables — in one statement.

INSERT ALL
    INTO customers (customer_id, customer_name, region, credit_limit)
         VALUES ('C008', 'Henry Co', 'East', 45000)
    INTO customers (customer_id, customer_name, region, credit_limit)
         VALUES ('C009', 'Ivan Trading', 'East', 32000)
    INTO customers (customer_id, customer_name, region, credit_limit)
         VALUES ('C010', 'Julia Wholesale', 'West', 60000)
SELECT * FROM dual;

Three rows inserted in one statement. The trailing SELECT * FROM dual is required — Oracle uses the one-row dual table to terminate the statement.

The full syntax also supports conditional inserts with WHEN clauses. That means the same INSERT ALL statement can route different rows to different tables based on conditions:

INSERT ALL
    WHEN credit_limit >= 50000 THEN
        INTO premium_customers VALUES (customer_id, customer_name, credit_limit)
    WHEN credit_limit < 50000 THEN
        INTO standard_customers VALUES (customer_id, customer_name, credit_limit)
SELECT customer_id, customer_name, credit_limit FROM customers;

That reads every customer and inserts them into one of two tables based on their credit limit. It is a complete routing operation in a single statement.

For non-Oracle databases, the multi-row VALUES form is the portable equivalent. Same effect, slightly different syntax.

INSERT INTO ... SELECT ... copies rows from one table to another in a single statement. It is the standard way to move data between tables without writing per-row logic in application code.

INSERT INTO customers_backup (customer_id, customer_name, region, credit_limit)
SELECT customer_id, customer_name, region, credit_limit
FROM   customers
WHERE  region = 'North';

Every North-region customer gets copied into the backup table. On our data, that is Alice Corp, Charlie Ltd, and George Wholesale. The other four customers stay in the original table only.

The WHERE clause controls which rows get copied. Without it, every row copies over. The column list on both sides must match in count and be type-compatible, though the names do not need to match. That is what lets you map between tables with different schemas:

INSERT INTO customer_summary (id, name, total_limit)
SELECT customer_id, customer_name, credit_limit
FROM   customers;

The target columns are named differently but the values line up positionally. The insert succeeds as long as the types are compatible.

This is the pattern for archiving, for building summary tables, and for moving data between environments. Whenever you find yourself writing application code that loops through rows and inserts them one by one, check whether an INSERT INTO ... SELECT would do the same job in a fraction of the code.

ROLLBACK undoes every uncommitted change in the current transaction. It is the undo button for DML, and it works until you COMMIT.

DELETE FROM orders WHERE order_status = 'Pending';
-- Realise this was a mistake
ROLLBACK;

The pending orders come back. Every statement since the last COMMIT — or since the start of the session if there was none — is undone.

ROLLBACK is what makes DML safe. You can experiment. You can run a DELETE that matches more rows than you expected. You can run an UPDATE with the wrong WHERE clause. As long as you have not committed, you can undo everything.

The window closes the moment you COMMIT. Any DML after the COMMIT is a new transaction, and rolling back only undoes the new work. The changes before the COMMIT are permanent.

Some databases put the session into "autocommit" mode by default — every statement commits itself immediately, and ROLLBACK has nothing to work with. MySQL is set up this way out of the box. Oracle is not. SQL Server, PostgreSQL, and MySQL are configurable. Know which mode your session is in before you rely on ROLLBACK.

COMMIT makes every change in the current transaction permanent. It ends the transaction, and the changes become visible to other sessions.

UPDATE customers SET credit_limit = credit_limit * 1.1 WHERE region = 'North';
COMMIT;

The three North-region customers have their new credit limits. The changes are now permanent and cannot be rolled back.

Before COMMIT, the changes exist in your session but are invisible to other users. They can be rolled back. After COMMIT, they are part of the database and available to everyone.

When you should commit: at the end of a logical unit of work, when everything has succeeded. When you should not: in the middle of a multi-step operation that could still fail. If a downstream step fails, rolling back is only possible if you have not already committed the earlier steps.

The anti-pattern to avoid: committing inside a loop. Every COMMIT has overhead — flushing to disk, updating transaction logs. A loop that commits after every row on a million-row operation runs a million COMMITs and takes hours. Batch the changes and commit once at the end.

A SAVEPOINT marks a point inside a transaction you can return to. Rolling back to a savepoint undoes everything after it without affecting anything before it.

UPDATE customers SET credit_limit = 55000 WHERE customer_id = 'C001';
SAVEPOINT after_alice;

UPDATE customers SET credit_limit = 30000 WHERE customer_id = 'C002';
SAVEPOINT after_bob;

UPDATE customers SET credit_limit = 90000 WHERE customer_id = 'C003';

-- Realise the third update was wrong
ROLLBACK TO SAVEPOINT after_bob;

COMMIT;

The rollback undoes Charlie Ltd's update — everything after after_bob. Alice Corp's and Bob Traders's updates are preserved because they were before the savepoint. The COMMIT then makes those two permanent.

Savepoints are how you handle long, multi-step transactions where an early step succeeded but a later step failed. Without them, the choice is all-or-nothing: discard the whole transaction or live with the mistake.

The syntax is standard across most databases. Oracle and PostgreSQL use SAVEPOINT name and ROLLBACK TO SAVEPOINT name. SQL Server uses SAVE TRANSACTION name and ROLLBACK TRANSACTION name. The behaviour is the same; the words differ slightly.

One thing to know: savepoints are cleaned up when the transaction ends. A COMMIT or full ROLLBACK discards all savepoints in the session. They only exist within the lifetime of the transaction that created them.

When the new value depends on data from another table, you join the tables and update in a single statement. The syntax varies between databases.

-- MySQL
UPDATE customers c
JOIN   orders o ON c.customer_id = o.customer_id
SET    c.credit_limit = o.order_amount * 3
WHERE  o.order_id = 1004;

Charlie Ltd's order 1004 is 22,000. His credit limit becomes 66,000.

-- PostgreSQL
UPDATE customers c
SET    credit_limit = o.order_amount * 3
FROM   orders o
WHERE  c.customer_id = o.customer_id
  AND  o.order_id = 1004;

Same effect, different syntax. PostgreSQL puts the joined table in the FROM clause.

-- Oracle and SQL Server (portable form)
UPDATE customers c
SET    credit_limit = (
    SELECT o.order_amount * 3
    FROM   orders o
    WHERE  o.customer_id = c.customer_id
      AND  o.order_id = 1004
)
WHERE  EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.customer_id AND o.order_id = 1004
);

The WHERE EXISTS is essential. Without it, every customer row would get updated. Customers with no matching order would have their credit limit set to NULL, because the correlated subquery returns NULL when it finds nothing.

The rule to remember whenever you update with a join or subquery: always add a WHERE EXISTS clause that matches the same filter. It prevents the "update every row" bug that has probably destroyed more production data than any other single mistake.

You use a CTE with a window function to number the rows within each duplicate group, then delete every row except the first.

WITH numbered AS (
    SELECT order_id,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id, order_amount
               ORDER BY order_id
           ) AS rn
    FROM   orders
)
DELETE FROM orders
WHERE  order_id IN (SELECT order_id FROM numbered WHERE rn > 1);

The CTE assigns a row number within each group of duplicates — rows that share the same customer and amount. The DELETE removes every row with a row number above 1, keeping the earliest one in each group.

To keep the latest instead of the earliest, change the ORDER BY inside the window function to DESC:

ROW_NUMBER() OVER (
    PARTITION BY customer_id, order_amount
    ORDER BY order_id DESC
)

Now the highest order_id in each duplicate group survives.

Before running any deduplication, run the SELECT version first to see what the duplicates look like:

SELECT customer_id, order_amount, COUNT(*) AS occurrences
FROM   orders
GROUP BY customer_id, order_amount
HAVING COUNT(*) > 1;

That tells you how many duplicates exist and which combinations are involved. If the answer surprises you, investigate before proceeding — sometimes what looks like a duplicate is actually two legitimately separate rows.

MERGE does insert-or-update in a single statement. It is how you synchronise a staging table with a production table without writing the check in application code.

MERGE INTO customers c
USING staging_customers s
    ON (c.customer_id = s.customer_id)
WHEN MATCHED THEN
    UPDATE SET c.credit_limit = s.credit_limit
WHEN NOT MATCHED THEN
    INSERT (customer_id, customer_name, region, credit_limit)
    VALUES (s.customer_id, s.customer_name, s.region, s.credit_limit);

Every row in the staging table gets compared against the target table by customer_id. If a matching row exists, the target's credit limit is updated. If not, a new row is inserted.

The reason this matters for data engineering: ETL pipelines regularly load fresh data into a warehouse. New rows need to be inserted; existing rows need updating. Before MERGE, this took two statements and a check — which had a race condition when two processes loaded the same row at the same time. MERGE does the whole job atomically.

MERGE also supports DELETE when a matched row should be removed instead of updated:

WHEN MATCHED AND s.is_deleted = 'Y' THEN
    DELETE

That makes MERGE a complete synchronisation tool — insert new rows, update changed rows, delete removed rows, all in one statement.

Support varies. Oracle, SQL Server, DB2, and PostgreSQL 15+ support MERGE natively. MySQL uses INSERT ... ON DUPLICATE KEY UPDATE for the same purpose with different syntax.

A subquery in a DELETE lets you remove rows based on a condition that depends on another table.

DELETE FROM customers
WHERE  customer_id IN (
    SELECT customer_id FROM orders WHERE order_amount > 20000
);

The subquery identifies customers with large orders. The DELETE removes them. On our data, only Charlie Ltd — his order 1004 is 22,000.

The alternative uses EXISTS, which is often faster on large tables because it can stop as soon as it finds a match:

DELETE FROM customers c
WHERE  EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.customer_id
      AND o.order_amount > 20000
);

Same result, potentially faster on big datasets.

One caveat worth knowing: some databases do not allow a DELETE with a subquery that references the same table being deleted from. If you hit "ORA-00907" or similar, that is usually why. The workaround is to use a CTE that materialises the list first, then delete based on the CTE's result.

You simply omit the columns that have defaults from the INSERT statement. The database fills them in.

INSERT INTO customers (customer_id, customer_name)
VALUES ('C011', 'Kelly Foods');

If the region column has a default of 'Unknown' and the credit_limit column has a default of 0, those values are applied automatically. Kelly Foods gets a region of Unknown and a credit limit of zero.

You can also ask the database to use defaults explicitly with the DEFAULT keyword:

INSERT INTO customers (customer_id, customer_name, region, credit_limit)
VALUES ('C011', 'Kelly Foods', DEFAULT, DEFAULT);

That form is useful when you are building INSERT statements programmatically and want to be explicit about which columns use defaults versus which ones have real values.

Columns without a default and without a value in the INSERT get NULL. That is why defining defaults on columns that should never be NULL is important — otherwise every insert that omits them gets NULL.

A CASE expression inside the SET clause lets you apply different new values to different rows based on conditions. One statement, many outcomes.

UPDATE customers
SET    credit_limit = CASE
           WHEN region = 'North' THEN credit_limit * 1.10
           WHEN region = 'South' THEN credit_limit * 1.05
           WHEN region = 'West'  THEN credit_limit * 1.02
           ELSE credit_limit
       END;

Each row gets a different increase depending on its region. North customers get 10%, South 5%, West 2%, and everyone else stays the same.

The CASE expression can also handle NULL explicitly:

UPDATE customers
SET    credit_limit = CASE
           WHEN credit_limit IS NULL THEN 10000
           WHEN credit_limit < 30000  THEN credit_limit * 1.15
           ELSE credit_limit
       END;

Fiona Mart's NULL becomes 10,000. Bob Traders (25,000) gets a 15% bump to 28,750. Everyone else is untouched.

The one thing to remember about CASE: it stops at the first matching branch. If you put the NULL check last, earlier numeric comparisons would never match for NULL rows — because NULL is not less than 30,000. The NULL check has to come first.

CASE in an UPDATE runs as a single atomic operation. Either all the branches apply or none do. Running three separate UPDATE statements for the same intent does not have that guarantee.

Bulk delete is just a DELETE with a WHERE clause that matches many rows. The database removes them all in one statement.

DELETE FROM orders
WHERE  order_status = 'Delivered'
  AND  order_amount < 10000;

That removes every delivered order under 10,000. On our data, that is order 1003 (7,500) and order 1007 (9,000). The other delivered orders — 1001 (12,000) and 1005 (3,200, but the status is Delivered so it qualifies) — hmm, let me trace this carefully. Delivered orders are 1001 (12,000), 1003 (7,500), 1005 (3,200), 1007 (9,000). The condition is delivered AND under 10,000, so 1003, 1005, and 1007 all qualify. Three rows removed.

Multiple conditions combine with AND and OR to build precise filters. Subqueries extend the same pattern:

DELETE FROM orders
WHERE  customer_id IN (
    SELECT customer_id FROM customers WHERE region = 'North'
);

That removes every order belonging to North-region customers. Alice Corp, Charlie Ltd, and George Wholesale are in the North, so their orders — 1001, 1003, 1004, 1007 — all go.

The performance of a bulk delete on a large table depends on whether the WHERE clause can use an index. A delete filtered by an indexed column is fast. A delete filtered by an unindexed column has to scan every row to decide what to remove.

For very large deletes — tens of millions of rows — one statement can lock the table for a long time and fill the transaction log. In those cases, deleting in batches with a loop (say, ten thousand rows per statement, committed between batches) is the safer approach. It takes longer overall but keeps the table available and the transaction log manageable.