Inserting and Modifying Data

Data enters and changes through DML statements:

Concept

Data enters and changes through DML statements:

Change values in existing rows MERGE / upsert Insert or update depending on whether the row exists

This module focuses on INSERT in depth and covers UPDATE and DELETE patterns you will use daily. To keep the sample database unchanged, run the experiments inside a transaction and roll back:

BEGIN;           -- START TRANSACTION in MySQL; BEGIN TRANSACTION in SQL Server
-- ... experiment ...
ROLLBACK;

Why It Matters

Incorrect inserts create bad data that is expensive to clean later.

A missing WHERE in UPDATE/DELETE is one of the most common production incidents.

Bulk-loading techniques (multi-row INSERT, INSERT ... SELECT) are essential in data engineering.

Syntax

-- Insert one row
INSERT INTO table_name (col1, col2, ...) VALUES (val1, val2, ...);

-- Insert several rows
INSERT INTO table_name (col1, col2) VALUES (v1, v2), (v3, v4), (v5, v6);

-- Insert the result of a query
INSERT INTO target_table (col1, col2)
SELECT colA, colB FROM source_table WHERE condition;

-- Update
UPDATE table_name
SET col1 = expr1, col2 = expr2
WHERE condition;

-- Delete
DELETE FROM table_name
WHERE condition;

Example

6.1 Single-row INSERT with an explicit column list 6.2 INSERT without a column list (not recommended)

This depends on the exact column order of the table. If someone later adds or reorders a column, this statement breaks or, worse, puts values into the wrong columns silently (when types are compatible).

6.3 Multiple-row INSERT Supported in PostgreSQL, MySQL, SQL Server (up to 1000 rows per VALUES list), SQLite 3.7.11+, Oracle 23ai+. For older Oracle:

A multi-row INSERT is a single statement: if one row fails a constraint, the whole statement fails and no rows are inserted (statement-level atomicity).

6.4 INSERT with selected columns (defaults and NULLs fill the rest)

Omitted columns receive their DEFAULT value, or NULL if there is no default. If an omitted column is NOT NULL without a default, the insert fails.

6.5 INSERT... SELECT Copy or transform data from one table into another.

Result in customer_archive:

(Row order in a table is not guaranteed; shown sorted for readability. archived_on is whatever today's date is.) 6.6 Handling NULL on insert

Write NULL without quotes. 'NULL' is a four-character string.

An explicit NULL overrides the column's DEFAULT.

6.7 Default values on insert 6.8 Returning generated values All assignments in a single UPDATE see the old row values (standard behavior):

The safe routine for UPDATE/DELETE:

INSERT INTO customers (customer_id, name, email, city, country)
VALUES (6, 'Farah Khan', '[email protected]', 'Hyderabad', 'India');

INSERT INTO customers
VALUES (7, 'Gopal Rao', '[email protected]', 'Chennai', 'India');

INSERT INTO products (product_id, product_name, category, price, stock) VALUES
(107, 'Keyboard',   'Electronics', 1500.00,  80),
(108, 'Webcam',     'Electronics', 3200.00,  40),
(109, 'Filing Box', 'Stationery',   450.00, 120);

-- Oracle (pre-23ai)
INSERT ALL
    INTO products (product_id, product_name, category, price, stock) VALUES (107, 'Keyboard', 'Electronics', 1500, 80)
    INTO products (product_id, product_name, category, price, stock) VALUES (108, 'Webcam', 'Electronics', 3200, 40)
SELECT * FROM dual;

INSERT INTO products (product_id, product_name, category, price)
VALUES (110, 'USB Hub', 'Electronics', 1200.00);
-- stock is omitted → DEFAULT 0 is used

CREATE TABLE customer_archive (
    customer_id  INT PRIMARY KEY,
    name         VARCHAR(100),
    country      VARCHAR(50),
    archived_on  DATE
);

INSERT INTO customer_archive (customer_id, name, country, archived_on)
SELECT customer_id, name, country, CURRENT_DATE
FROM customers
WHERE country <> 'India';

INSERT INTO customers (customer_id, name, email, city, country)
VALUES (8, 'Hari Menon', NULL, 'Kochi', 'India');   -- explicit NULL: email unknown

INSERT INTO products (product_id, product_name, category, price, stock)
VALUES (111, 'Pen Stand', 'Stationery', 150.00, DEFAULT);   -- uses DEFAULT 0

-- Insert a row made entirely of defaults (PostgreSQL, SQL Server, SQLite)
INSERT INTO support_tickets DEFAULT VALUES;   -- only works if every column has a default/identity

-- PostgreSQL, SQLite 3.35+, MariaDB 10.5+
INSERT INTO invoices (amount) VALUES (2500.00) RETURNING invoice_id;

-- SQL Server
INSERT INTO invoices (amount) OUTPUT INSERTED.invoice_id VALUES (2500.00);

-- MySQL
INSERT INTO invoices (amount) VALUES (2500.00);
SELECT LAST_INSERT_ID();

-- Oracle (PL/SQL)
INSERT INTO invoices (amount) VALUES (2500) RETURNING invoice_id INTO :new_id;

-- Give all Sales employees a 10% raise
UPDATE employees
SET salary = salary * 1.10
WHERE dept_id = 2;

-- Update several columns
UPDATE orders
SET status = 'DELIVERED',
    total_amount = total_amount     -- unchanged, shown for syntax
WHERE order_id = 1003;

-- Update using a value from another table
-- Standard SQL (correlated subquery) — works everywhere
UPDATE order_items
SET unit_price = (SELECT p.price FROM products p WHERE p.product_id = order_items.product_id)
WHERE order_id = 1005;

-- PostgreSQL: UPDATE ... FROM
UPDATE order_items oi
SET unit_price = p.price
FROM products p
WHERE p.product_id = oi.product_id
  AND oi.order_id = 1005;

-- MySQL: UPDATE with JOIN
UPDATE order_items oi
JOIN products p ON p.product_id = oi.product_id
SET oi.unit_price = p.price
WHERE oi.order_id = 1005;

-- SQL Server: UPDATE ... FROM with alias
UPDATE oi
SET oi.unit_price = p.price
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
WHERE oi.order_id = 1005;

Expected Result

After running inside a transaction:

Run ROLLBACK; to return to the original sample data.

UPDATE employees SET salary = salary * 1.10 WHERE dept_id = 2;
SELECT emp_id, first_name, salary FROM employees WHERE dept_id = 2 ORDER BY emp_id;

Common Mistakes

Omitting the column list in INSERT.

Quoting numbers or NULL ('NULL', '100') — causes implicit conversions or wrong data.

UPDATE employees SET salary = 0; — forgot WHERE, every row updated.

Deleting parent rows before child rows when foreign keys exist (FK violation), or relying on CASCADE unknowingly.

Using a date literal in a non-ISO format ('05/01/2026') whose meaning depends on server settings.

Inserting duplicate rows because there is no UNIQUE constraint on the natural key.

Forgetting that MySQL evaluates SET assignments left to right.

Best Practices

Always specify the column list.

Use multi-row INSERT or bulk loaders (COPY in PostgreSQL, LOAD DATA in MySQL, BULK INSERT/bcp in SQL Server, SQL*Loader in Oracle) for large loads.

Preview with SELECT, run inside a transaction, and check affected row counts.

Use RETURNING/OUTPUT instead of a separate query to fetch generated IDs.

Batch very large UPDATE/DELETE operations (e.g., 10,000 rows per transaction) to avoid long locks and huge logs.

Interview Relevance

Medium to high: "How do you insert multiple rows?", "What is INSERT ... SELECT?", "How do you update from another table?", "How do you get the last inserted ID?", "How do you safely delete data?".

Practice Questions

Insert three new departments in one statement.

Insert a product using only the required columns and let stock default.

Copy all DELIVERED orders into a new table delivered_orders using INSERT ... SELECT.

Increase the price of all Furniture products by 5%.

Delete order items whose quantity is greater than 10 (none should match — verify first with SELECT).

Why does INSERT INTO t (a) VALUES (NULL) not use a's DEFAULT?