Structured Query Language/INSERT 2
< Structured Query Language
Hint: Be carefull and deactivate AUTOCOMMIT.
The basic syntax and semantic of the INSERT command is described on the page INSERT. There are examples how to insert single rows with fixed values into a table. The present page describes how to dynamise the command by the use of subqueries.
Evaluate Values at Runtime
First, the values to be inserted may be evaluated in a relative 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 absolutly 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), -- The scalar value subquery. It computes one single value, in this case 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
Similar to the above shown evaluation of a single scalar value through 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 within one single statement. In addition to its dynamic nature it saves all but one round-trips 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 absolutly 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 has change in such a way that the key word 'VALUES' with its list of values is replaced by a complete subquery (often named 'subselect') which starts with the key word 'SELECT'. Of course the number and type of the selected columns must correlate with the number and type of the columns of the specified column list behind the 'INSERT INTO' key word. 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. Therefore 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
DELETE FROM person WHERE id > 100;
COMMIT;
Exercises
Insert 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;