Quiz: SQL INSERT, UPDATE and DELETE: DML Commands in MySQL
Study the lesson first: 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?
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: afterALTER TABLE ... ADD COLUMNthe old command fails withERROR 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 bymax_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?
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
DEFAULTvalue; a column that allowsNULLand has no explicitDEFAULTgetsNULL. - An
AUTO_INCREMENTcolumn gets the next number when it is omitted or receivesNULL(or0). - The keyword
DEFAULTin 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?
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
INSERTit returns the id of the first inserted row; - any following insert with
AUTO_INCREMENToverwrites 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?
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
SELECTcolumns must match theINSERTcolumn list. - A constant in the
SELECT(here2) 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 withINSERT IGNORE). - The table in the
SELECTmay 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?
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 IGNOREskips the row and raises a warning instead of an error (0 rows affected per row). It is dangerous because it also suppresses other errors:NULLin aNOT NULLcolumn, 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. AnAUTO_INCREMENTnumber is used up even on update.REPLACEdeletes the old row and inserts a new one (2 rows affected or more). It is a realDELETE:ON DELETE CASCADE/SET NULLand delete triggers fire, a referenced parent row without such an action givesERROR 1451, and columns not listed in the command get theirDEFAULT.
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?
How do UPDATE and DELETE work in MySQL: WHERE, Rows matched vs Changed, ORDER BY and LIMIT?
UPDATEchanges the columns inSET(several, separated by commas; a value can be an expression such asviews = views + 1) in every row that matchesWHERE;DELETEremoves those rows, the table stays. WithoutWHERE, every row is affected.Rows matchedis how many rows the condition found,Changedis how many actually changed. Repeating anUPDATEwith the same values givesChanged: 0.- In MySQL, single-table
UPDATEandDELETEacceptORDER BYandLIMIT;LIMITwithoutORDER BYpicks arbitrary rows. Large volumes are deleted in batches: repeatDELETE ... ORDER BY id LIMIT 1000until0 rows affected. - If a foreign key refers to the key being changed or the row being deleted, the result depends on its action (
ERROR 1451is 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?
What are the most common mistakes in INSERT, UPDATE and DELETE, and how do you avoid them?
UPDATE/DELETEwithoutWHEREchanges or deletes every row. Run aSELECTwith 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 = NULLfinds no rows (Rows matched: 0): a comparison withNULLis never true. UseWHERE phone IS NULL.- An apostrophe inside a string,
'O'Brien', givesERROR 1064– double it ('O''Brien'), and in Java usePreparedStatementparameters instead of string concatenation. INSERT IGNORE“just in case” silently skips rows with a foreign key violation and turnsNULLinto an empty string or zero – useON DUPLICATE KEY UPDATEor handle error 1062 in code.REPLACEinstead of an upsert deletes the row: cascades fire, error 1451 is possible, and without an explicitidthe 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.