Last modified on 12 October 2014, at 11:08

Structured Query Language/INSERT 2

Views Structured Query Language
INSERT 2
UPDATE_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 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 RuntimeEdit

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 RuntimeEdit

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 DatabaseEdit

DELETE FROM person WHERE id > 100;
COMMIT;

ExercisesEdit

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;


Views Structured Query Language
INSERT 2
UPDATE_2