Inserting, Updating, and Deleting Data

MySQL supports standard INSERT, UPDATE, and DELETE, plus powerful extensions: INSERT IGNORE, REPLACE, INSERT ...

Concept

MySQL supports standard INSERT, UPDATE, and DELETE, plus powerful extensions: INSERT IGNORE, REPLACE, INSERT ... ON DUPLICATE KEY UPDATE, multi-table UPDATE/DELETE, LIMIT on UPDATE/DELETE, and fast bulk loading with LOAD DATA.

Why It Matters

Data modification is where correctness and safety matter most: accidental mass updates, silent duplicate handling, and slow bulk loads are everyday problems.

Syntax

INSERT INTO t (c1, c2) VALUES (v1, v2) [, (v3, v4) ...];
INSERT INTO t (c1, c2) SELECT ...;
INSERT INTO t SET c1 = v1, c2 = v2;                                    -- MySQL form
INSERT IGNORE INTO t (...) VALUES (...);
REPLACE INTO t (...) VALUES (...);
INSERT INTO t (...) VALUES (...) AS new ON DUPLICATE KEY UPDATE c2 = new.c2;   -- 8.0.19+ alias syntax

UPDATE t SET c1 = expr [, c2 = expr] [WHERE ...] [ORDER BY ...] [LIMIT n];
UPDATE t1 JOIN t2 ON ... SET t1.c = t2.c WHERE ...;
DELETE FROM t [WHERE ...] [ORDER BY ...] [LIMIT n];
DELETE t1 FROM t1 JOIN t2 ON ... WHERE ...;

Example

Run experiments inside a transaction so the sample data stays unchanged:

8.2 INSERT... SELECT 8.3 Duplicate handling: INSERT IGNORE, REPLACE, ON DUPLICATE KEY UPDATE On duplicate key INSERT IGNORE Row skipped with a warning

Also turns data errors (truncation, NULL into NOT NULL) into warnings Old row deleted, new row inserted

New AUTO_INCREMENT id, lost columns, cascaded deletes, triggers fire as DELETE+INSERT ON DUPLICATE KEY UPDATE Existing row updated

If a table has several unique keys, the "matched" row may be unexpected

The older form ON DUPLICATE KEY UPDATE price = VALUES(price) still works but VALUES() in this context is deprecated since 8.0.20.

8.4 Bulk loading with LOAD DATA LOAD DATA is typically 10–20× faster than individual INSERTs. Check SHOW VARIABLES LIKE 'secure_file_priv'; for the allowed server directory. MySQL Shell's util.import_table() performs parallel imports.

MySQL evaluates single-table SET assignments left to right, using already-updated values:

"Rows matched" vs "changed": MySQL reports Rows matched: 2 Changed: 1 when one row already had the new value; ROW_COUNT() returns changed rows by default.

A subquery on the same table in DELETE/UPDATE raises ERROR 1093 ("You can't specify target table for update in FROM clause"). Wrap it in a derived table:

8.7 Returning generated keys MySQL has no RETURNING clause (MariaDB does). Use:

or the connector's API (cursor.lastrowid in Python, getGeneratedKeys() in JDBC).

START TRANSACTION;
-- experiments
ROLLBACK;

INSERT INTO products (product_id, product_name, category, price, stock)
VALUES (107, 'Keyboard', 'Electronics', 1500.00, 80);

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

INSERT INTO products SET product_id = 110, product_name = 'USB Hub', category = 'Electronics', price = 1200.00;
-- stock takes its DEFAULT 0

SELECT ROW_COUNT();   -- rows affected by the previous statement

CREATE TABLE customer_archive LIKE customers;
INSERT INTO customer_archive
SELECT * FROM customers WHERE country <> 'India';      -- 3 rows: Ben, Chen, Elena

-- 1. INSERT IGNORE: skip rows that violate PK/UNIQUE (and downgrade some other errors to warnings!)
INSERT IGNORE INTO products (product_id, product_name, category, price, stock)
VALUES (101, 'Laptop v2', 'Electronics', 70000, 5);
-- Query OK, 0 rows affected, 1 warning (Duplicate entry '101' for key 'products.PRIMARY')

-- 2. REPLACE: DELETE the old row, then INSERT the new one
REPLACE INTO products (product_id, product_name, category, price, stock)
VALUES (106, 'Standing Desk', 'Furniture', 34000, 2);
-- 2 rows affected (1 delete + 1 insert). Columns not listed get defaults; ON DELETE CASCADE children are deleted!
-- Because the old row is DELETEd first, REPLACE on a product that order_items references (e.g. 102) fails:
-- ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
--   (`eh_company`.`order_items`, CONSTRAINT `fk_items_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`product_id`))

-- 3. Upsert: update selected columns when the key exists (preferred)
INSERT INTO products (product_id, product_name, category, price, stock)
VALUES (102, 'Mouse', 'Electronics', 750, 250) AS new
ON DUPLICATE KEY UPDATE price = new.price, stock 
…

-- File on the SERVER host (requires FILE privilege and secure_file_priv directory)
LOAD DATA INFILE '/var/lib/mysql-files/dtc_events.csv'
INTO TABLE dtc_events
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(vehicle_id, ecu_id, dtc_code, @detected, status)
SET detected_at = STR_TO_DATE(@detected, '%Y-%m-%d %H:%i:%s');

-- File on the CLIENT machine (requires local_infile enabled on server and client)
LOAD DATA LOCAL INFILE 'C:/data/can_frames.csv' INTO TABLE can_frames
FIELDS TERMINATED BY ',' IGNORE 1 LINES
(@ts, @can_id_hex, dlc, @payload_hex)
SET captured_at = @ts,
    can_id      = CONV(@can_id_hex, 16, 10),
    payload     = UNHEX(@payload_hex);

SELECT order_id, customer_id, total_amount
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM orders;

UPDATE employees SET salary = salary * 1.10 WHERE dept_id = 2;      -- 2 rows

-- Multi-table UPDATE (MySQL syntax)
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;

-- UPDATE with ORDER BY and LIMIT (single-table only): process in batches
UPDATE dtc_events SET status = 'ARCHIVED'
WHERE detected_at < '2025-01-01' AND status = 'CLEARED'
ORDER BY dtc_event_id
LIMIT 10000;                         -- repeat until ROW_COUNT() = 0

UPDATE t SET a = b, b = a;   -- MySQL: both become the old b (not a swap)

DELETE FROM order_items WHERE order_id = 1004;

-- Multi-table DELETE: delete order items of cancelled orders
DELETE oi
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
WHERE o.status = 'CANCELLED';

-- Delete in batches
DELETE FROM can_frames WHERE captured_at < '2025-01-01' ORDER BY frame_id LIMIT 50000;

DELETE FROM customers
WHERE customer_id IN (
    SELECT id FROM (SELECT customer_id AS id FROM customers WHERE email IS NULL) AS x
);

Expected Result

START TRANSACTION;
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;
ROLLBACK;

+--------+------------+-----------+
| emp_id | first_name | salary    |
+--------+------------+-----------+
|      4 | Rahul      | 132000.00 |
|      5 | Priya      |  66000.00 |
+--------+------------+-----------+

Common Mistakes

UPDATE/DELETE without WHERE.

REPLACE used as "upsert", causing cascaded deletes and new IDs.

INSERT IGNORE hiding truncation and NULL errors.

ERROR 1093 when referencing the target table in a subquery.

Forgetting LOCAL in LOAD DATA when the file is on the client.

Huge single-transaction deletes that bloat undo logs and lag replicas.

Best Practices

Preview with SELECT, then modify inside a transaction.

Use ON DUPLICATE KEY UPDATE for upserts.

Batch large updates/deletes with LIMIT.

Use LOAD DATA or multi-row inserts (hundreds to thousands of rows per statement) for bulk loads.

Keep sql_safe_updates on for interactive sessions.

Interview Relevance

High: "REPLACE vs INSERT ... ON DUPLICATE KEY UPDATE?", "What does INSERT IGNORE do?", "How do you bulk load CSV into MySQL?", "How do you update from another table in MySQL?", "What is error 1093?".

Practice Questions

Insert three new products in one statement.

Write an upsert that adds 50 to the stock of product 104 or inserts it if missing.

Increase Furniture prices by 5% using a single UPDATE.

Delete order items for products in the Stationery category using a multi-table DELETE.

Load a CSV of DTC events whose dates are in DD/MM/YYYY format.