Structured Query Language/INSERT 2
Hint: Be carefull and deactivate AUTOCOMMIT.
The basic syntax and semantic of the INSERT command is described on the page INSERT. There are examples of how to insert single rows with fixed values into a table. The present page describes how to dynamize the command by the use of subqueries.
Evaluate Values at Runtime
editFirst, the values to be inserted may be evaluated in a relatively strict way by reading the system time or other (quasi) constants.
-- Use the key word CURRENT_DATE to determine the actual day.
INSERT INTO person ( id, firstname, lastname, date_of_birth, place_of_birth, ssn, weight)
VALUES (101, 'Larry, no. 101', 'Goldstein', CURRENT_DATE, 'Dallas', '078-05-1120', 95);
COMMIT;
Next, the values to be inserted may be evaluated by a scalar value subquery. This means, that single values may be computed at runtime based on the rows of the same or another table.
-- Count the number of rows to determine the next ID. Caution: This handling of IDs is absolutely NOT recommended for real applications!
INSERT INTO person ( id, firstname, lastname, date_of_birth, place_of_birth, ssn, weight)
VALUES ((SELECT COUNT(*) + 1000 FROM person), -- This scalar-value subquery computes a single value, the new ID.
-- VALUES ((Select * FROM (SELECT COUNT(*) + 1000 FROM person) tmp), -- MySQL insists in using an intermediate table
'Larry, no. ?', 'Goldstein', CURRENT_DATE, 'Dallas', '078-05-1120', 95);
COMMIT;
Evaluate Rows at Runtime
editSimilar to the previous example of a single scalar value from a scalar value subquery, one can use a table subquery to get several rows and insert them into the specified table within one INSERT command. This version is able to insert thousands of rows with one single statement. In addition to its dynamic nature, it saves all but one round-trip between the application and the DBMS and therefore is much faster than a lot of single row-based INSERTs.
-- The statement doubles the number of rows within the table. It omits in the table subquery the WHERE clause and therefore
-- it reads all existing rows. Caution: This handling of IDs is absolutely NOT recommended for real applications!
INSERT INTO person (id, firstname, lastname, date_of_birth, place_of_birth, ssn, weight)
SELECT id + 1100, firstname, lastname, date_of_birth, place_of_birth, ssn, weight
FROM person;
COMMIT;
The syntax is changed such that a complete subquery replaces the keyword 'VALUES' with its list of values (often named 'subselect'), which starts with the keyword 'SELECT'. Of course, the number and type of the selected columns must correlate with the number and type of the columns in the specified column list that follows the 'INSERT INTO' keyword. Within the subquery, the complete power of the SELECT statement may be used: JOIN, WHERE, GROUP BY, ORDER BY, and especially other subqueries in a recursive manner. There is a wide range of use cases: create rows with increased version numbers, with percentage increased salary, with the actual timestamp, fixed values from rows of the same or another table, ... .
-- The next two statements compute different weights depending on the old weight
INSERT INTO person (id, firstname, lastname, date_of_birth, place_of_birth, ssn, weight)
-- the subquery starts here
SELECT id + 1200, firstname, lastname, date_of_birth, place_of_birth, ssn,
CASE WHEN weight < 40 THEN weight + 10
ELSE weight + 5
END
FROM person
WHERE id <= 10; -- only the original 10 rows from the example database
COMMIT;
-- The same semantic with a more complex syntax (to demonstrate the power of subselect)
INSERT INTO person (id, firstname, lastname, date_of_birth, place_of_birth, ssn, weight)
-- the first subquery starts here
SELECT id + 1300, firstname, lastname, date_of_birth, place_of_birth, ssn,
-- here starts a subquery of the first subquery. The CASE construct evaluates different
-- weights depending on the old weight.
(SELECT CASE WHEN weight < 40 THEN weight + 10
ELSE weight + 5
END
FROM person ssq -- alias for the table name in sub-subquery
WHERE sq.id = ssq.id -- link the rows together
)
FROM person sq -- alias for the table name in subquery
WHERE id <= 10; -- only the original 10 rows from the example database
COMMIT;
The technique shown at Structured Query Language/Example_Database_Data#Grow_up, which multiplies existing data, e.g., for testing purpose, is based on such table subqueries.
Clean up Your Database
editDELETE FROM person WHERE id > 100;
COMMIT;
Exercises
editInsert a new person with id 1301, firstname 'Mr. Mean', lastname is the lowest lastname (in the sense of the character encoding, use min() function). Its weight is the average weight of all persons (use avg() function).
-- Two columns are computed during runtime
INSERT INTO person (id, firstname, lastname, weight)
VALUES (1301,
'Mr. Mean',
(SELECT MIN(lastname) FROM person),
(SELECT AVG(weight) FROM person)
-- the MySQL version with its intermediate tables
-- (SELECT * FROM (SELECT MIN(lastname) FROM person) tmp1),
-- (SELECT * FROM (SELECT AVG(weight) FROM person) tmp2)
);
COMMIT;
-- Check your result
SELECT * FROM person WHERE id = 1301;
Insert one additional person per family (=lastname) with firstname 'An extraordinary family member', lastname is the family name. Incorporate only the rows from the original example database with id <= 10.
-- Two columns are computed during runtime. The number of involved rows is delimited by the WHERE clause.
INSERT INTO person (id, firstname, lastname)
-- here starts the subquery
SELECT MAX(id) + 1310, -- in this case the max() function works per group
'An extraordinary family member',
lastname
FROM person
WHERE id <= 10
GROUP BY lastname;
COMMIT;
-- Check your result
SELECT * FROM person WHERE id > 1310;
Clean up your database.
DELETE FROM person WHERE id > 1300;
COMMIT;