Tutorials

INSERT

You will need to add new records to your database tables. To do this you use an INSERT query.

Let's take a look at adding a new pupil. The pupil table is defined like this:

mysql> describe pupil;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| pupilID | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| fname   | varchar(45)      | NO   |     | NULL    |                |
| lname   | varchar(45)      | NO   |     | NULL    |                |
| houseID | int(10) unsigned | NO   | MUL | 1       |                |
| classid | char(1)          | NO   | MUL | A       |                |
+---------+------------------+------+-----+---------+----------------+

INSERT statements are amongst the simplest of SQL statements, comprising

  • INSERT keyword
  • table name
  • affected column names
  • values for the columns
  • option to IGNORE errors

The query to add a new pupil called Joanne Doe to house 2 and class B would be

INSERT INTO pupil (fname, lname, houseid, classid)
VALUES ('Joanne', 'Doe', 2, 'B')

Examining the query

  1. INSERT INTO pupil
    defines the table into which we are inserting a new record
  2. (fname, lname, houseid, classid)
    defines the columns for which we are defining new values
  3. VALUES ('Joanne', 'Doe', 2, 'B')
    defines the values for the columns listed in 2. There must be a value defined for each column name in the list. Note that the pupilID field is defined as auto_increment (its value is generated automatically) and therefore is excluded from the statement.

Variations on the query

INSERT INTO pupil VALUES (NULL, 'Joanne', 'Doe', 2, 'B')

No column names have been specified so you must provide a value for every column in the table and in the same order that they are defined in the table. (Not advisable as the query will fail if you or someone else subsequently adds another column to the table.) In the case of the auto_increment field we pass a NULL value.

INSERT INTO pupil (fname, lname)
VALUES ('Joanne', 'Doe')

A record for Joanne Doe is added. As no values are provided for the house and class ids, and the fields are defined as NOT NULL, the default values (1 and A) will be used.

INSERT INTO pupil (houseid, classid)
VALUES (3, 'D')

No values are provided for the first and last name fields. As the fields are defined as NOT NULL, and the default values are NULL then the query will fail. In other words, the name fields cannot be left blank.

Inserting multiple records

The most efficient way to insert several records (perhaps a thousand at a time) is to use a multiple insert query. The syntax is similar to the single record insert but with sets of values for each record separated by commas. For example

INSERT INTO pupil (fname, lname, houseid, classid)
VALUES 
('Joanne', 'Doe', 2, 'B'),
('Johnny', 'Rotten', 1, 'C'),
('Helen', 'Troy', 2, 'D'),
('Walter', 'Mitty', 4, 'C'),
('Sherlock', 'Holmes', 3, 'D');

INSERT ... SELECT

It is possible to insert records into a table where the values come from the records in another table.

Beetsem-Daley recently bought the local academy to add to his education empire. The academy provided us with a table contining their pupil names so we could transfer them to our pupil table.

Their table:

mysql> describe academy_pupils;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| firstname | varchar(30) | YES  |     | NULL    |                |
| lastname  | varchar(30) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+

The houses and classes would be updated later. We first created a new temporary house record to assign to the new pupils. This would make it easy to identify the new records.

INSERT INTO house (house_name, house_master) VALUES ('Academy', 12)

then retrieved the auto-generated id of the new house with

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                5 |
+------------------+

The new data was then loaded with

INSERT INTO pupil (fname, lname, houseid)
    SELECT firstname
        , lastname
        , 5
    FROM academy_pupils

Handling duplicates

When inserting records there is a chance that the information you are are inserting already exists in the database table. There are various strategies for dealing woth this situation. We'll use the choice table which records the subjects chosen by each pupil. As you can see, a unique key constraint has been defined on pupil/subject so that the same choice isn't recorded a second time

CREATE TABLE `choice` (                                |       mysql> SELECT pupilid, subjectid
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,       |           -> FROM CHOICE
  `pupilID` int(10) unsigned NOT NULL,                 |           -> LIMIT 3;
  `subjectID` int(10) unsigned NOT NULL,               |       +---------+-----------+
  PRIMARY KEY (`id`),                                  |       | pupilid | subjectid |
  UNIQUE KEY `pupil` (`pupilID`,`subjectID`),          |       +---------+-----------+
  KEY `subject` (`subjectID`)                          |       |       1 |         1 |
)                                                      |       |       1 |         2 |
                                                       |       |       1 |         5 |
                                                       |       +---------+-----------+

Strategy 1 - Check for errors

Attempting to insert an already existing choice will result in the query failing. You can then test for a duplicate key error.

mysql> INSERT INTO choice(pupilid,subjectid) VALUES (1,5);

ERROR 1062 (23000): Duplicate entry '1-5' for key 'pupil'

Strategy 2 - Ignoring the duplicate key error

The Headmaster, Beetsem-Daley, decided that all pupils at the school should take maths (subject #4). We therefore need to add subject 4 for all students to the choice table. The problem is that some pupils already have maths in their list of choices

mysql> SELECT c.pupilid, fname, lname
    -> FROM choice c
    -> JOIN pupil USING (pupilid)
    -> WHERE subjectid=4;
+---------+---------+------------+
| pupilid | fname   | lname      |
+---------+---------+------------+
|       7 | David   | Powell     |
|      10 | Henry   | Irving     |
|      13 | John    | Tully      |
|      19 | Mary    | Whitehouse |
|      22 | Peter   | Appleby    |
|      23 | Wayne   | Jones      |
|      24 | William | Smith      |
+---------+---------+------------+

so the query would fail with duplicate key errors.

The solution is to tell the query to IGNORE errors and continue inserting records

mysql> INSERT IGNORE INTO choice (pupilid, subjectid)
    -> SELECT pupilid, 4
    -> FROM pupil;
Query OK, 17 rows affected (0.01 sec)
Records: 24  Duplicates: 7  Warnings: 0

Strategy 3 - Update the record that is already there

You can specify that if a duplicate key already exists in the table then, instead of trying to insert the new one, the query should update the existing recosd with the new data

To do this, use the ON DUPLICATE KEY UPDATE option and specify the fields that should be updated

Example, trying to reinsert pupil 15 with revised first name

mysql> SELECT * FROM pupil WHERE pupilid=15;
+---------+-------+----------+---------+---------+
| pupilID | fname | lname    | houseID | classid |
+---------+-------+----------+---------+---------+
|      15 | John  | Williams |       2 | C       |
+---------+-------+----------+---------+---------+

mysql> INSERT INTO pupil (pupilid, fname, lname) VALUES (15, 'Jason', 'Williams')
    -> ON DUPLICATE KEY UPDATE
    -> fname = 'Jason';

mysql> SELECT * FROM pupil WHERE pupilid=15;
+---------+-------+----------+---------+---------+
| pupilID | fname | lname    | houseID | classid |
+---------+-------+----------+---------+---------+
|      15 | Jason | Williams |       2 | C       |
+---------+-------+----------+---------+---------+

An alternative to specifying the value twice is to use the VALUES() function. This is necessary if using the multiple insert syntax and is also more convenient if using prepared queries in your code (as it saves duplicating placeholders). In the example below, VALUES(fname) returns the value specified for the column fname.

INSERT INTO pupil VALUES (15, 'Joseph', 'Williams', 2, 'C')
ON DUPLICATE KEY UPDATE
fname = VALUES(fname)
lname = VALUES(lname)
houseid = VALUES(houseid)
classid = VALUES(classid);

Alternative SET syntax

MySQL also permits a non-standard insert syntax using

SET col_name = col_value

instead of

(column_names) VALUES (column_values)

Standard version

    INSERT INTO pupil (fname, lname, houseid, classid)
    VALUES ('Joanne', 'Doe', 2, 'B')

Alternative version

    INSERT INTO pupil
    SET fname = 'Joanne'
        , lname = 'Doe'
        , houseid = 2
        , classid = 'B';

As you will see, the alternative version is very similar to the UPDATE syntax