MySQL INSERT

INSERT INTO ... VALUES

The INSERT INTO statement adds new rows to a table. You can specify the column list explicitly (recommended) or rely on column order. Always list columns explicitly to make your code resilient to future schema changes.

INSERT Single and Multiple Rows

-- Insert a single row (explicit column list)
INSERT INTO customers (first_name, last_name, email, phone, active)
VALUES ('Alice', 'Johnson', 'alice@example.com', '555-0101', 1);

-- Insert using SET syntax (alternative, column = value pairs)
INSERT INTO customers SET
    first_name = 'Bob',
    last_name  = 'Smith',
    email      = 'bob@example.com',
    active     = 1;

-- Insert multiple rows in a single statement (much faster than multiple INSERTs)
INSERT INTO customers (first_name, last_name, email, active)
VALUES
    ('Carol', 'White',  'carol@example.com',  1),
    ('Dave',  'Brown',  'dave@example.com',   1),
    ('Eve',   'Davis',  'eve@example.com',    0),
    ('Frank', 'Wilson', 'frank@example.com',  1);

-- Get the AUTO_INCREMENT ID of the last inserted row
SELECT LAST_INSERT_ID();

INSERT INTO ... SELECT

You can insert rows from the result of a SELECT query. This is useful for copying data between tables, creating summary tables, or archiving records.

INSERT INTO ... SELECT and INSERT IGNORE

-- Copy active customers to an archive table
INSERT INTO customers_archive (first_name, last_name, email, active)
SELECT first_name, last_name, email, active
FROM customers
WHERE active = 1;

-- INSERT IGNORE: skip rows that would cause a duplicate key error
-- (useful when importing data that may have duplicates)
INSERT IGNORE INTO customers (first_name, last_name, email, active)
VALUES ('Alice', 'Johnson', 'alice@example.com', 1);
-- If alice@example.com already exists (UNIQUE key), this row is silently skipped

-- Insert products into the shop
INSERT INTO products (name, description, price, stock, category)
VALUES
    ('Laptop Pro 15',   'High-performance laptop',  1299.99, 50,  'Electronics'),
    ('Wireless Mouse',  'Ergonomic wireless mouse',   29.99, 200, 'Electronics'),
    ('USB-C Hub',       '7-in-1 USB-C hub',           49.99, 150, 'Electronics'),
    ('Desk Lamp',       'LED adjustable desk lamp',   39.99, 100, 'Office'),
    ('Notebook A5',     'Premium lined notebook',      9.99, 500, 'Stationery');

INSERT ... ON DUPLICATE KEY UPDATE

This statement inserts a row if no duplicate key exists, or updates the existing row if a duplicate is found. It's an efficient "upsert" pattern that avoids a separate SELECT check.

ON DUPLICATE KEY UPDATE (Upsert)

-- Assume email has a UNIQUE constraint on customers
-- If the email already exists, update the name and active status instead
INSERT INTO customers (first_name, last_name, email, active)
VALUES ('Alice', 'Johnson-Updated', 'alice@example.com', 1)
ON DUPLICATE KEY UPDATE
    first_name = VALUES(first_name),
    last_name  = VALUES(last_name),
    active     = VALUES(active);

-- MySQL 8.0+ syntax using aliases (cleaner)
INSERT INTO customers (first_name, last_name, email, active)
VALUES ('Alice', 'Johnson-Updated', 'alice@example.com', 1) AS new_row
ON DUPLICATE KEY UPDATE
    first_name = new_row.first_name,
    last_name  = new_row.last_name,
    active     = new_row.active;

-- Track stock changes: increment stock on duplicate product name
INSERT INTO products (name, price, stock, category)
VALUES ('Wireless Mouse', 29.99, 50, 'Electronics')
ON DUPLICATE KEY UPDATE
    stock = stock + VALUES(stock);

Previous Next

MySQL INSERT

INSERT INTO ... VALUES

INSERT INTO ... SELECT

INSERT ... ON DUPLICATE KEY UPDATE

Ready to Level Up Your Skills?