SQL INSERT, UPDATE, DELETE: Modifying Data Safely

Reading data is only half of the story. The other half is writing it. In this article you will learn the three data modification statements — INSERT, UPDATE, and DELETE — along with the safety mechanisms that protect your data: transactions, constraints, and conflict handling.

We continue with the same e-commerce schema from the previous article (products, customers, orders).

INSERT INTO

INSERT adds new rows to a table.

Syntax

SQL

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

The column list and the values list must match in count and order.

Inserting a Single Row

SQL

INSERT INTO products (product_id, name, category, price, stock, is_active, created_at)
VALUES (11, 'Mouse Pad XL', 'Accessories', 19.99, 150, TRUE, '2024-04-17');

If you are inserting values for every column in the table's column order, you can omit the column list — but this is brittle and not recommended for production code:

SQL

-- Fragile: breaks if table structure changes
INSERT INTO products
VALUES (12, 'Desk Lamp', 'Office', 44.99, 75, TRUE, '2024-04-17');

Columns with Default Values

You can omit columns that have DEFAULT values defined:

SQL

-- stock defaults to 0, is_active defaults to TRUE
INSERT INTO products (product_id, name, category, price, created_at)
VALUES (13, 'Cable Organizer', 'Accessories', 9.99, '2024-04-17');

Inserting Multiple Rows

You can insert several rows in a single statement by providing multiple VALUES tuples:

SQL

INSERT INTO customers (customer_id, first_name, last_name, email, country, signup_date)
VALUES
    (9,  'Iris',  'Clark',   'iris@example.com',   'US', '2024-04-10'),
    (10, 'Jack',  'Lewis',   'jack@example.com',   'GB', '2024-04-11'),
    (11, 'Karen', 'Walker',  'karen@example.com',  'AU', '2024-04-12');

Multi-row inserts are more efficient than running three separate INSERT statements because there is only one round-trip to the database.

INSERT ... SELECT

You can populate a table by selecting from another table. This is extremely useful for data migrations, creating summary tables, or copying filtered subsets.

SQL

-- Archive cancelled orders to a separate table
CREATE TABLE cancelled_orders AS SELECT * FROM orders WHERE 1=0;  -- create empty copy

INSERT INTO cancelled_orders
SELECT *
FROM orders
WHERE status = 'cancelled';

A more realistic business scenario: creating a restock needed list:

SQL

CREATE TABLE restock_list (
    product_id  INT,
    name        VARCHAR(200),
    stock       INT,
    flagged_at  TIMESTAMP DEFAULT NOW()
);

INSERT INTO restock_list (product_id, name, stock)
SELECT product_id, name, stock
FROM products
WHERE stock < 50
  AND is_active = TRUE;

The SELECT inside an INSERT can use all the power of SELECT: JOINs, WHERE filters, expressions, aggregates.

UPDATE

UPDATE modifies existing rows.

Basic Syntax

SQL

UPDATE table_name
SET column1 = value1,
    column2 = value2
WHERE condition;

Critical rule: always include a WHERE clause unless you genuinely want to update every row. Without WHERE, UPDATE affects every single row in the table.

Single Column Update

SQL

-- Apply a 10% discount to all Stationery products
UPDATE products
SET price = price * 0.90
WHERE category = 'Stationery';

Multiple Column Update

SQL

-- Reactivate the Monitor Stand and set a new price
UPDATE products
SET is_active = TRUE,
    price     = 42.99
WHERE product_id = 7;

Update with an Expression

You can use the current column value on the right side of the assignment:

SQL

-- Reduce all Electronics prices by $5
UPDATE products
SET price = price - 5.00
WHERE category = 'Electronics';

-- Double the stock for any product below 50 units
UPDATE products
SET stock = stock * 2
WHERE stock < 50;

Update with a Subquery in WHERE

SQL

-- Mark orders as 'archived' for customers who have no city on file
UPDATE orders
SET status = 'archived'
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    WHERE city IS NULL
);

Update with a Correlated Subquery

A correlated subquery references columns from the outer UPDATE:

SQL

-- Set each order's unit_price to match the current product price
-- (useful after a bulk price change)
UPDATE orders o
SET unit_price = (
    SELECT price
    FROM products p
    WHERE p.product_id = o.product_id
)
WHERE status = 'pending';

UPDATE with JOIN (SQL Server / PostgreSQL)

Some databases let you update one table based on a JOIN with another:

SQL

-- SQL Server: update order status based on product availability
UPDATE o
SET o.status = 'on_hold'
FROM orders o
JOIN products p ON p.product_id = o.product_id
WHERE p.stock = 0
  AND o.status = 'pending';

-- PostgreSQL equivalent using FROM clause
UPDATE orders o
SET status = 'on_hold'
FROM products p
WHERE p.product_id = o.product_id
  AND p.stock = 0
  AND o.status = 'pending';

DELETE

DELETE removes rows from a table.

Basic Syntax

SQL

DELETE FROM table_name
WHERE condition;

Again: always use WHERE unless you mean to delete all rows.

Delete Specific Rows

SQL

-- Remove a specific cancelled order
DELETE FROM orders
WHERE order_id = 8;

-- Delete all cancelled orders
DELETE FROM orders
WHERE status = 'cancelled';

Delete with a Subquery

SQL

-- Delete orders for products that have been deactivated
DELETE FROM orders
WHERE product_id IN (
    SELECT product_id
    FROM products
    WHERE is_active = FALSE
);

DELETE vs TRUNCATE vs DROP

These three operations are often confused.

| Operation | What it does | Can be rolled back? | Resets identity/sequence? | |-----------|---------------------------------------|---------------------|---------------------------| | DELETE | Removes specific rows, or all rows | Yes (in transaction)| No | | TRUNCATE | Removes all rows, very fast | Sometimes (DB-specific) | Yes | | DROP | Removes the entire table (structure too) | Sometimes (DB-specific) | N/A |

DELETE is the most flexible. It respects WHERE, fires triggers, and is always transactional.

SQL

-- Delete all orders (but keep the table structure)
DELETE FROM orders;

TRUNCATE is much faster for large tables because it deallocates data pages rather than logging each row deletion. Use it when you want to wipe a table clean and reset sequences.

SQL

-- PostgreSQL: truncate and reset the sequence
TRUNCATE TABLE orders RESTART IDENTITY;

-- SQL Server
TRUNCATE TABLE orders;  -- IDENTITY automatically resets

TRUNCATE cannot be used when there are foreign key constraints referencing the table (unless you truncate the child tables first).

DROP removes everything: the data and the table definition.

SQL

DROP TABLE restock_list;           -- no recovery!
DROP TABLE IF EXISTS restock_list; -- safe version

Transactions: BEGIN, COMMIT, ROLLBACK

A transaction is a group of SQL statements that execute as a single unit. Either all of them succeed (COMMIT) or none of them take effect (ROLLBACK). This is the foundation of data integrity.

Why Transactions Matter

Imagine transferring money between two accounts:

SQL

UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
-- What if the server crashes right here?
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;

Without a transaction, account 1 loses $500 but account 2 never gains it. With a transaction, either both happen or neither does.

Basic Transaction Syntax

SQL

BEGIN;  -- or BEGIN TRANSACTION; or START TRANSACTION;

UPDATE products SET stock = stock - 1 WHERE product_id = 1;
INSERT INTO orders (order_id, customer_id, product_id, quantity, unit_price, status, order_date)
VALUES (11, 1, 1, 1, 89.99, 'pending', CURRENT_DATE);

COMMIT;  -- make changes permanent

If something goes wrong, roll back:

SQL

BEGIN;

UPDATE products SET stock = stock - 1 WHERE product_id = 1;
-- Suppose we detect an error here...
ROLLBACK;  -- undo all changes since BEGIN

SAVEPOINT: Partial Rollback

SAVEPOINTs let you roll back to a point within a transaction without abandoning everything:

SQL

BEGIN;

INSERT INTO orders (order_id, customer_id, product_id, quantity, unit_price, status, order_date)
VALUES (12, 2, 3, 1, 129.99, 'pending', CURRENT_DATE);

SAVEPOINT before_stock_update;

UPDATE products SET stock = stock - 1 WHERE product_id = 3;

-- If stock goes negative, roll back just the UPDATE
ROLLBACK TO SAVEPOINT before_stock_update;

-- The INSERT is still pending; we can commit or roll back further
COMMIT;

Auto-Commit

Most database clients run in auto-commit mode by default, meaning each statement is its own transaction. To group statements you must explicitly use BEGIN.

In application code, always wrap multi-step write operations in an explicit transaction.

Transaction Example: Processing an Order

SQL

BEGIN;

-- Deduct stock
UPDATE products
SET stock = stock - 2
WHERE product_id = 1
  AND stock >= 2;  -- only if sufficient stock

-- Check if the update actually changed a row
-- (in a real app you'd check affected rows count here)

-- Insert the order
INSERT INTO orders (order_id, customer_id, product_id, quantity, unit_price, status, order_date)
VALUES (13, 3, 1, 2, 89.99, 'pending', CURRENT_DATE);

COMMIT;

Constraints

Constraints are rules enforced by the database engine that prevent invalid data from being stored.

PRIMARY KEY

Uniquely identifies each row. Cannot be NULL. A table can have only one primary key (though it can span multiple columns).

SQL

CREATE TABLE products (
    product_id INT PRIMARY KEY,  -- single column
    ...
);

-- Multi-column (composite) primary key
CREATE TABLE order_items (
    order_id    INT,
    product_id  INT,
    quantity    INT,
    PRIMARY KEY (order_id, product_id)
);

Attempting to insert a duplicate primary key fails immediately:

SQL

INSERT INTO products (product_id, name, category, price, created_at)
VALUES (1, 'Duplicate', 'Electronics', 99.99, CURRENT_DATE);
-- ERROR: duplicate key value violates unique constraint "products_pkey"

FOREIGN KEY

Enforces referential integrity between tables. The value in the foreign key column must exist in the referenced table.

SQL

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers(customer_id),
    product_id  INT NOT NULL REFERENCES products(product_id),
    ...
);

Trying to insert an order for a non-existent customer fails:

SQL

INSERT INTO orders VALUES (99, 9999, 1, 1, 89.99, 'pending', CURRENT_DATE);
-- ERROR: insert or update on table "orders" violates foreign key constraint

ON DELETE behavior: you can control what happens when a referenced row is deleted:

SQL

-- Cascade: delete orders when the customer is deleted
customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADE

-- Set null: null out the FK when referenced row is deleted
customer_id INT REFERENCES customers(customer_id) ON DELETE SET NULL

-- Restrict (default): prevent deletion if child rows exist
customer_id INT REFERENCES customers(customer_id) ON DELETE RESTRICT

UNIQUE

Ensures all values in a column (or combination of columns) are distinct.

SQL

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    email       VARCHAR(200) UNIQUE NOT NULL,  -- no duplicate emails
    ...
);

Multi-column unique constraint:

SQL

-- A customer can only review each product once
ALTER TABLE reviews
ADD CONSTRAINT uq_customer_product UNIQUE (customer_id, product_id);

NOT NULL

Prevents a column from storing NULL.

SQL

name  VARCHAR(200) NOT NULL,  -- must always have a name
email VARCHAR(200) NOT NULL   -- must always have an email

Trying to insert NULL into a NOT NULL column:

SQL

INSERT INTO products (product_id, name, category, price, created_at)
VALUES (14, NULL, 'Electronics', 49.99, CURRENT_DATE);
-- ERROR: null value in column "name" violates not-null constraint

CHECK

Enforces a custom condition on column values.

SQL

CREATE TABLE products (
    product_id  INT PRIMARY KEY,
    name        VARCHAR(200) NOT NULL,
    price       DECIMAL(10,2) CHECK (price >= 0),        -- no negative prices
    stock       INT           CHECK (stock >= 0),          -- no negative stock
    category    VARCHAR(100)  CHECK (category IN ('Electronics','Office','Stationery','Accessories'))
);

Multi-column CHECK:

SQL

CREATE TABLE orders (
    ...
    quantity    INT           NOT NULL,
    unit_price  DECIMAL(10,2) NOT NULL,
    CONSTRAINT chk_positive_order CHECK (quantity > 0 AND unit_price > 0)
);

DEFAULT

Provides a value when no value is specified during INSERT.

SQL

CREATE TABLE products (
    stock      INT     NOT NULL DEFAULT 0,
    is_active  BOOLEAN NOT NULL DEFAULT TRUE,
    created_at DATE    NOT NULL DEFAULT CURRENT_DATE
);

With defaults defined, you can omit those columns in INSERT:

SQL

INSERT INTO products (product_id, name, category, price)
VALUES (15, 'Sticky Notes', 'Stationery', 3.99);
-- stock = 0, is_active = TRUE, created_at = today

Adding Constraints to Existing Tables

SQL

-- Add a NOT NULL constraint
ALTER TABLE customers ALTER COLUMN email SET NOT NULL;

-- Add a CHECK constraint
ALTER TABLE products ADD CONSTRAINT chk_price_positive CHECK (price >= 0);

-- Add a UNIQUE constraint
ALTER TABLE customers ADD CONSTRAINT uq_email UNIQUE (email);

-- Drop a constraint
ALTER TABLE products DROP CONSTRAINT chk_price_positive;

ON CONFLICT and UPSERT

UPSERT is a portmanteau of UPDATE + INSERT. It means: "insert this row, but if it already exists, update it instead."

PostgreSQL: ON CONFLICT

SQL

-- If product_id already exists, update the price and stock
INSERT INTO products (product_id, name, category, price, stock, created_at)
VALUES (1, 'Wireless Headphones', 'Electronics', 79.99, 150, CURRENT_DATE)
ON CONFLICT (product_id)
DO UPDATE SET
    price  = EXCLUDED.price,
    stock  = EXCLUDED.stock;

EXCLUDED refers to the row that was proposed for insertion but was rejected due to the conflict.

DO NOTHING: ignore the insert silently if a conflict occurs:

SQL

-- Add a product only if it doesn't already exist
INSERT INTO products (product_id, name, category, price, created_at)
VALUES (1, 'Wireless Headphones', 'Electronics', 89.99, CURRENT_DATE)
ON CONFLICT (product_id) DO NOTHING;

Conflict on a unique constraint:

SQL

-- Prevent duplicate email registration
INSERT INTO customers (customer_id, first_name, last_name, email, country, signup_date)
VALUES (9, 'Iris', 'Clark', 'alice@example.com', 'US', CURRENT_DATE)
ON CONFLICT (email)
DO UPDATE SET
    first_name = EXCLUDED.first_name,
    last_name  = EXCLUDED.last_name;

SQL Server: MERGE

SQL Server uses MERGE for upsert:

SQL

MERGE INTO products AS target
USING (VALUES (1, 'Wireless Headphones', 'Electronics', 79.99, 150))
    AS source (product_id, name, category, price, stock)
ON target.product_id = source.product_id
WHEN MATCHED THEN
    UPDATE SET
        target.price = source.price,
        target.stock = source.stock
WHEN NOT MATCHED THEN
    INSERT (product_id, name, category, price, stock, created_at)
    VALUES (source.product_id, source.name, source.category, source.price, source.stock, GETDATE());

Returning Modified Data

PostgreSQL's RETURNING clause lets you get the modified rows back without a separate SELECT query:

SQL

-- Get the new order_id after insert (useful with SERIAL/auto-generated IDs)
INSERT INTO orders (customer_id, product_id, quantity, unit_price, status, order_date)
VALUES (1, 2, 1, 34.99, 'pending', CURRENT_DATE)
RETURNING order_id, order_date;

-- Get updated prices after bulk discount
UPDATE products
SET price = price * 0.85
WHERE category = 'Electronics'
RETURNING product_id, name, price AS new_price;

-- Get the deleted row for audit logging
DELETE FROM orders
WHERE order_id = 5
RETURNING *;

SQL Server equivalent using OUTPUT:

SQL

-- INSERT with OUTPUT
INSERT INTO orders (customer_id, product_id, quantity, unit_price, status, order_date)
OUTPUT INSERTED.order_id, INSERTED.order_date
VALUES (1, 2, 1, 34.99, 'pending', GETDATE());

-- UPDATE with OUTPUT
UPDATE products
SET price = price * 0.85
OUTPUT DELETED.price AS old_price, INSERTED.price AS new_price, INSERTED.name
WHERE category = 'Electronics';

Practical Examples

Example 1: Add a new product with validation

SQL

BEGIN;

-- Insert the product
INSERT INTO products (product_id, name, category, price, stock, is_active, created_at)
VALUES (16, 'Blue Light Glasses', 'Accessories', 29.99, 100, TRUE, CURRENT_DATE);

-- Verify it was inserted correctly
SELECT * FROM products WHERE product_id = 16;

COMMIT;

Example 2: Bulk price adjustment with audit

SQL

BEGIN;

-- Create a price history snapshot before changing
INSERT INTO price_history (product_id, old_price, changed_at)
SELECT product_id, price, NOW()
FROM products
WHERE category = 'Electronics';

-- Apply the price change
UPDATE products
SET price = ROUND(price * 1.05, 2)  -- 5% price increase
WHERE category = 'Electronics';

COMMIT;

Example 3: Safely delete a customer and their data

SQL

BEGIN;

-- Delete child records first to avoid FK violations
DELETE FROM orders WHERE customer_id = 5;

-- Now delete the customer
DELETE FROM customers WHERE customer_id = 5;

COMMIT;

Example 4: Sync inventory from a staging table

SQL

BEGIN;

-- Staging table has the latest stock counts
INSERT INTO products (product_id, name, category, price, stock, created_at)
SELECT product_id, name, category, price, stock, CURRENT_DATE
FROM staging_products
ON CONFLICT (product_id)
DO UPDATE SET
    stock      = EXCLUDED.stock,
    price      = EXCLUDED.price,
    is_active  = TRUE;

-- Mark products not in staging as inactive
UPDATE products
SET is_active = FALSE
WHERE product_id NOT IN (SELECT product_id FROM staging_products);

COMMIT;

Common Mistakes

Mistake 1: UPDATE or DELETE without WHERE

SQL

-- DESTROYS ALL ROWS
DELETE FROM orders;

-- UPDATES ALL ROWS
UPDATE products SET price = 0;

Always test your WHERE clause with a SELECT first:

SQL

-- Step 1: verify which rows you'll affect
SELECT * FROM products WHERE category = 'Stationery';

-- Step 2: run the UPDATE
UPDATE products SET price = price * 0.90 WHERE category = 'Stationery';

Mistake 2: Inserting into a table with FK constraints in the wrong order

SQL

-- WRONG: order references customer_id = 99 which doesn't exist
INSERT INTO orders VALUES (20, 99, 1, 1, 89.99, 'pending', CURRENT_DATE);
-- ERROR: foreign key violation

-- RIGHT: insert the customer first
INSERT INTO customers VALUES (99, 'Test', 'User', 'test@example.com', 'US', CURRENT_DATE);
INSERT INTO orders VALUES (20, 99, 1, 1, 89.99, 'pending', CURRENT_DATE);

Mistake 3: Not wrapping multi-step operations in a transaction

If two related updates need to succeed together, always use a transaction. Never rely on "it probably won't fail halfway through."

Mistake 4: TRUNCATE vs DELETE confusion

SQL

-- TRUNCATE cannot be used when there are FK references
TRUNCATE TABLE customers;  -- ERROR if orders references customers
-- DELETE still works (though it logs each row)
DELETE FROM customers;     -- Still violates FK unless orders is empty

Key Takeaways

INSERT INTO ... VALUES adds rows; multi-row inserts are more efficient than repeated single inserts.
INSERT ... SELECT copies data from one query into a table — great for migrations and staging.
UPDATE ... SET ... WHERE modifies rows; without WHERE every row is affected.
DELETE FROM ... WHERE removes rows; without WHERE all rows are removed.
TRUNCATE is faster than DELETE for clearing whole tables but resets sequences and cannot be rolled back in all databases.
DROP TABLE removes the table itself, not just its data.
Wrap related modifications in a BEGIN / COMMIT transaction so partial failures can be rolled back.
Constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT) are the database's enforcement layer — use them, do not work around them.
ON CONFLICT DO UPDATE (PostgreSQL) and MERGE (SQL Server) implement upsert logic cleanly.
RETURNING (PostgreSQL) and OUTPUT (SQL Server) let you retrieve affected rows without a second query.

SQL INSERT, UPDATE, DELETE: Modifying Data Safely

INSERT INTO

Syntax

Inserting a Single Row

Columns with Default Values

Inserting Multiple Rows

INSERT ... SELECT

UPDATE

Basic Syntax

Single Column Update

Multiple Column Update

Update with an Expression

Update with a Subquery in WHERE

Update with a Correlated Subquery

UPDATE with JOIN (SQL Server / PostgreSQL)

DELETE

Basic Syntax

Delete Specific Rows

Delete with a Subquery

DELETE vs TRUNCATE vs DROP

Transactions: BEGIN, COMMIT, ROLLBACK

Why Transactions Matter

Basic Transaction Syntax

SAVEPOINT: Partial Rollback

Auto-Commit

Transaction Example: Processing an Order

Constraints

PRIMARY KEY

FOREIGN KEY

UNIQUE

NOT NULL

CHECK

DEFAULT

Adding Constraints to Existing Tables

ON CONFLICT and UPSERT

PostgreSQL: ON CONFLICT

SQL Server: MERGE

Returning Modified Data

Practical Examples

Example 1: Add a new product with validation

Example 2: Bulk price adjustment with audit

Example 3: Safely delete a customer and their data

Example 4: Sync inventory from a staging table

Common Mistakes

Mistake 1: UPDATE or DELETE without WHERE

Mistake 2: Inserting into a table with FK constraints in the wrong order

Mistake 3: Not wrapping multi-step operations in a transaction

Mistake 4: TRUNCATE vs DELETE confusion

Key Takeaways

Enjoyed this article?

Leave a comment