Quiz: SQL INSERT, UPDATE and DELETE: DML Commands in MySQL

Total: 7 questions

1. 

How do you add rows with INSERT INTO: with a column list, without one, and several rows at once?

INSERT is a DML command (together with UPDATE and DELETE) that adds rows without changing the table structure.

INSERT INTO users (username, email) VALUES ('Dave', '[email protected]');   -- with a column list
INSERT INTO users VALUES (NULL, 'Eve', '[email protected]', NULL);            -- without a list
INSERT INTO tags (name) VALUES ('java'), ('sql'), ('mysql');             -- three rows
  • With a column list, the values go into the listed columns in order, and the other columns get their default value. The number of values must equal the number of columns.
  • Without a list, you must give a value for every column strictly in declaration order, including id. This form is fragile: after ALTER TABLE ... ADD COLUMN the old command fails with ERROR 1136: Column count doesn't match value count at row 1. Application code should always list the columns.
  • Several rows are listed with commas after a single VALUES: one round trip to the server and one log write, so it is faster than separate commands. The command is atomic: if one row violates a constraint (for example, ERROR 1062), InnoDB inserts none of them. The command size is limited by max_allowed_packet (64 MB by default in MySQL 8), so large data sets are inserted in batches.

Strings and dates go in single quotes, numbers without quotes.

2. 

What value does a column omitted from INSERT get, and why does error 1364 “Field doesn't have a default value” occur?

  • An omitted column gets its DEFAULT value; a column that allows NULL and has no explicit DEFAULT gets NULL.
  • An AUTO_INCREMENT column gets the next number when it is omitted or receives NULL (or 0).
  • The keyword DEFAULT in the value list explicitly asks for the default value:
INSERT INTO topics (title, author_id) VALUES ('Ice fishing', 1);
-- the same:
INSERT INTO topics (id, title, author_id, created_at, views)
    VALUES (NULL, 'Ice fishing', 1, DEFAULT, DEFAULT);

A NOT NULL column without a default cannot be skipped:

INSERT INTO users (username) VALUES ('Frank');
-- ERROR 1364 (HY000): Field 'email' doesn't have a default value

The error comes from strict mode, STRICT_TRANS_TABLES, which is on by default in MySQL 8; without it MySQL would insert an empty string with a warning. An explicit NULL in a NOT NULL column gives a different error – 1048.

3. 

How do you get the id of the row you just inserted with LAST_INSERT_ID(), and what is the catch?

LAST_INSERT_ID() returns the AUTO_INCREMENT value generated by the last successful insert in the current connection:

  • inserts made by other connections do not affect it, so it is safe under concurrent load;
  • after a multi-row INSERT it returns the id of the first inserted row;
  • any following insert with AUTO_INCREMENT overwrites the value – including an insert into another table.

The catch: if you add a message with LAST_INSERT_ID() after inserting a thread, the function starts returning the message id, and a second message with the same expression gets a nonexistent topic_id – ERROR 1452 or a row linked to the wrong parent. Save the id right away:

INSERT INTO topics (title, author_id) VALUES ('Ice fishing', 1);
SET @topic_id = LAST_INSERT_ID();
INSERT INTO messages (topic_id, author_id, body) VALUES (@topic_id, 1, 'Is the ice thick enough yet?');
INSERT INTO messages (topic_id, author_id, body) VALUES (@topic_id, 3, 'Not yet, it is still thin.');

In Java the same number is returned by getGeneratedKeys() of a PreparedStatement created with the Statement.RETURN_GENERATED_KEYS flag.

4. 

How do you insert the result of a query into a table with INSERT ... SELECT?

Instead of VALUES, write a SELECT after the column list: every row of its result is inserted into the table. Tag all threads of user 1 with “sport” (id = 2):

INSERT INTO topic_tags (topic_id, tag_id)
SELECT id, 2 FROM topics WHERE author_id = 1;
-- Query OK, 2 rows affected
-- Records: 2  Duplicates: 0  Warnings: 0
  • Columns are matched by position, not by name: the number and order of the SELECT columns must match the INSERT column list.
  • A constant in the SELECT (here 2) is put into every row.
  • Constraints are checked as for a normal insert: running the same command again fails with ERROR 1062: Duplicate entry '1-2' for key 'topic_tags.PRIMARY' (duplicates can be skipped with INSERT IGNORE).
  • The table in the SELECT may be the same table you insert into: MySQL first stores the query result in a temporary table.
5. 

What is the difference between INSERT IGNORE, INSERT ... ON DUPLICATE KEY UPDATE and REPLACE in MySQL?

All three handle a duplicate primary or unique key without error 1062, but in different ways:

  • INSERT IGNORE skips the row and raises a warning instead of an error (0 rows affected per row). It is dangerous because it also suppresses other errors: NULL in a NOT NULL column, a foreign key violation, a string that is too long – data is silently corrupted or lost.
  • INSERT ... ON DUPLICATE KEY UPDATE (upsert) updates the existing row. Rows affected: 1 – inserted, 2 – updated, 0 – values unchanged. An AUTO_INCREMENT number is used up even on update.
  • REPLACE deletes the old row and inserts a new one (2 rows affected or more). It is a real DELETE: ON DELETE CASCADE / SET NULL and delete triggers fire, a referenced parent row without such an action gives ERROR 1451, and columns not listed in the command get their DEFAULT.
INSERT INTO users (username, email)
VALUES ('Bob', '[email protected]') AS new
ON DUPLICATE KEY UPDATE email = new.email;

The row alias AS new appeared in MySQL 8.0.19; the VALUES(email) function from older examples has been deprecated since 8.0.20 and raises warning 1287 (MariaDB understands only VALUES()). To update an existing row, choose ON DUPLICATE KEY UPDATE or UPDATE, not REPLACE.

6. 

How do UPDATE and DELETE work in MySQL: WHERE, Rows matched vs Changed, ORDER BY and LIMIT?

  • UPDATE changes the columns in SET (several, separated by commas; a value can be an expression such as views = views + 1) in every row that matches WHERE; DELETE removes those rows, the table stays. Without WHERE, every row is affected.
  • Rows matched is how many rows the condition found, Changed is how many actually changed. Repeating an UPDATE with the same values gives Changed: 0.
  • In MySQL, single-table UPDATE and DELETE accept ORDER BY and LIMIT; LIMIT without ORDER BY picks arbitrary rows. Large volumes are deleted in batches: repeat DELETE ... ORDER BY id LIMIT 1000 until 0 rows affected.
  • If a foreign key refers to the key being changed or the row being deleted, the result depends on its action (ERROR 1451 is possible).

Protection against a command without WHERE is safe update mode (SET SQL_SAFE_UPDATES = 1): UPDATE and DELETE without a key condition and without LIMIT fail with ERROR 1175.

7. 

What are the most common mistakes in INSERT, UPDATE and DELETE, and how do you avoid them?

  • UPDATE/DELETE without WHERE changes or deletes every row. Run a SELECT with the same condition first and make a risky change inside a transaction.
  • INSERT INTO t VALUES (...) without a column list breaks after a column is added (ERROR 1136) – list the columns.
  • WHERE phone = NULL finds no rows (Rows matched: 0): a comparison with NULL is never true. Use WHERE phone IS NULL.
  • An apostrophe inside a string, 'O'Brien', gives ERROR 1064 – double it ('O''Brien'), and in Java use PreparedStatement parameters instead of string concatenation.
  • INSERT IGNORE “just in case” silently skips rows with a foreign key violation and turns NULL into an empty string or zero – use ON DUPLICATE KEY UPDATE or handle error 1062 in code.
  • REPLACE instead of an upsert deletes the row: cascades fire, error 1451 is possible, and without an explicit id the row gets a new number.
  • Reusing LAST_INSERT_ID() after the next insert returns the id of another row – save it in a variable right away.
Page 1 of 1