Skip to content
Tutorials Center
My learning

Topics

Lesson 6 of 40 ยท SQL Basics

INSERT, UPDATE and DELETE in MariaDB

Add, change and remove rows in MariaDB with INSERT, UPDATE and DELETE, bulk inserts, INSERT ... SELECT, REPLACE, ON DUPLICATE KEY UPDATE, RETURNING and removing duplicate rows.

Reading data is only half of a database's job. The data manipulation statements INSERT, UPDATE and DELETE create and change rows. They are simple, and that is why mistakes with them are expensive: a missing WHERE clause changes every row. This lesson shows each statement, the MariaDB extensions around them, and the safety habits that go with them.

INSERT

The basic form names the columns and gives one row of values:

SQL
INSERT INTO products (name, price, stock)
VALUES ('Notebook', 3.50, 120);

You can insert several rows in one statement, which is much faster than many single inserts because the server parses and commits once:

SQL
INSERT INTO products (name, price, stock) VALUES
  ('Pen',    1.20, 500),
  ('Marker', 2.10, 250),
  ('Ruler',  0.90, 300);

Rules worth remembering:

  • Columns you leave out get their DEFAULT, or NULL if allowed, or an AUTO_INCREMENT value. If neither is possible the insert fails (strict mode).
  • Always list the column names. INSERT INTO products VALUES (...) breaks the day someone adds a column.
  • INSERT ... SET col = value, ... is a MySQL/MariaDB alternative syntax; the standard VALUES form is portable.
  • INSERT IGNORE skips rows that would raise errors (duplicates, bad data). It hides problems, so use it deliberately.

Insert from a query with INSERT ... SELECT:

SQL
INSERT INTO products_archive (id, name, price)
SELECT id, name, price FROM products WHERE stock = 0;

After an insert into an AUTO_INCREMENT table, LAST_INSERT_ID() returns the new key for your session:

SQL
INSERT INTO customers (first_name, last_name, email) VALUES ('Mia', 'Lopez', '[email protected]');
SELECT LAST_INSERT_ID();

MariaDB also supports RETURNING, which hands back inserted (and deleted) rows without a second query:

SQL
INSERT INTO customers (first_name, last_name, email)
VALUES ('Ravi', 'Nair', '[email protected]')
RETURNING id, email;

Runnable version of the basics (the editor starts from a clean table each run):

SQL Single and multi-row INSERT

Press Run (or Ctrl + Enter) to see the result here.

UPDATE

UPDATE changes existing rows. The WHERE clause picks which:

SQL
UPDATE products SET price = price * 1.10 WHERE category_id = 3;
UPDATE products SET stock = 0, price = 0 WHERE name = 'Old model';

An UPDATE without WHERE changes every row. Habit: write the WHERE first, or run the same condition as a SELECT before you change anything.

MariaDB adds ORDER BY and LIMIT to UPDATE and DELETE, handy for batches, plus a multi-table form:

SQL
UPDATE orders SET status = 'cancelled'
WHERE status = 'pending'
ORDER BY ordered_at
LIMIT 100;

UPDATE products AS p
JOIN categories AS c ON c.id = p.category_id
SET p.price = p.price * 0.9
WHERE c.name = 'Clearance';

Updating and replacing part of a string

REPLACE(text, from, to) swaps all occurrences, and CONCAT builds new values:

SQL
UPDATE customers SET email = REPLACE(email, '@old-mail.com', '@new-mail.com')
WHERE email LIKE '%@old-mail.com';
UPDATE products SET name = CONCAT('Legacy ', name) WHERE stock = 0;
SQL UPDATE with REPLACE ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL UPDATE with a calculation ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

DELETE

SQL
DELETE FROM orders WHERE status = 'cancelled' AND ordered_at < '2024-01-01';
DELETE FROM log ORDER BY created_at LIMIT 1000;    -- trim in batches
DELETE FROM products RETURNING id, name;           -- MariaDB 10.0+

DELETE without WHERE empties the table but is slower than TRUNCATE and keeps AUTO_INCREMENT. Foreign keys may refuse the delete, or cascade it, depending on how the constraint is defined (see keys and constraints).

For very large deletes, run them in batches of a few thousand rows so locks stay short:

SQL
DELETE FROM events WHERE created_at < '2023-01-01' LIMIT 5000;
-- repeat until 0 rows affected

REPLACE and upserts

Two MariaDB tools handle "insert, or update if it exists":

SQL
REPLACE INTO settings (name, value) VALUES ('theme', 'dark');

REPLACE deletes the old row with the same key and inserts a new one. It also resets columns you did not mention and fires delete triggers, which is rarely what you want. Prefer:

SQL
INSERT INTO settings (name, value) VALUES ('theme', 'dark')
ON DUPLICATE KEY UPDATE value = VALUES(value);

This updates only the named column when a unique key already exists. The standard-SQL cousin that SQLite also supports is ON CONFLICT:

SQL Upsert (the portable idea)

Press Run (or Ctrl + Enter) to see the result here.

Deleting duplicate rows

Duplicates appear when a table has no unique constraint. Keep the row with the smallest id and delete the rest:

SQL Remove duplicates, keep one

Press Run (or Ctrl + Enter) to see the result here.

On MariaDB you cannot select from the table you delete from in a plain subquery, so use a self join (the NOT IN above is allowed in SQLite but raises error 1093 in older MariaDB versions):

SQL
DELETE s1 FROM subscribers AS s1
JOIN subscribers AS s2 ON s1.email = s2.email AND s1.id > s2.id;

Afterwards add a UNIQUE key so duplicates cannot return.

Transactions as a safety net

With InnoDB, wrap risky changes in a transaction, check the result, then commit or roll back:

SQL
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
SELECT balance FROM accounts WHERE id = 1;   -- looks right?
ROLLBACK;                                      -- or COMMIT;

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What happens when an UPDATE statement has no WHERE clause?

    Without a condition every row matches. Always write the WHERE clause first, or test it with a SELECT.
  2. 2. Which is the better way to update an existing row or insert a new one, keeping other columns untouched?

    REPLACE deletes and reinserts the row, losing columns you did not list. ON DUPLICATE KEY UPDATE changes only what you name.
  3. 3. Why insert many rows with one multi-row INSERT?

    One statement means one parse and one commit, so there is far less overhead.
  4. 4. What does RETURNING do on an INSERT or DELETE in MariaDB?

    RETURNING avoids a second query to read keys or deleted values.
  5. 5. How do you delete 5,000 old rows at a time to keep locks short?

    MariaDB allows LIMIT on DELETE and UPDATE, so you can loop until no rows are affected.

Summary

  • INSERT adds rows (list the columns, prefer multi-row inserts, use INSERT ... SELECT to copy); LAST_INSERT_ID() and RETURNING give back new keys.
  • UPDATE and DELETE need a WHERE; MariaDB also allows ORDER BY and LIMIT, and multi-table forms.
  • Prefer ON DUPLICATE KEY UPDATE over REPLACE for upserts.
  • Remove duplicates by keeping MIN(id) per group, then add a UNIQUE key.

Next: SELECT, WHERE and ORDER BY