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 edit

First, 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 edit

Similar 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 edit

DELETE FROM person WHERE id > 100;
COMMIT;

Exercises edit

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).

Click to see solution
-- 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.

Click to see solution
-- 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.

Click to see solution
DELETE FROM person WHERE id > 1300;
COMMIT;