SQL DML COMMANDS
9 SQL DML C OMMANDS .1 Learning Objectives
This chapter provides the methods and tools needed to manipulate table data using the SQL Data Manipulation Language (DML) commands. At the completion of this chapter, you will be able to:
• Utilize the INSERT statement
• Utilize the UPDATE statement
• Utilize the REPLACE statement
• Utilize the DELETE statement 145
9.2 The INSERT Statement
Now that tables have been created, the next step is to populate them with data. Although there are a variety of ways to get data into MySQL tables, the INSERT statement is the most common method for getting data into a table. The INSERT statement uses the following general syntax;
INSERT INTO table_name (column_list) VALUES(value_list);
An insert can include a only the value(s) without a column list (if the exact column order, quantity and types are known), as follows;
INSERT INTO numbers VALUES(250);
Row contents will be as follows;
+---+
| n | +---+
| 250 | +---+
To add multiple column data into a table (containing an inventory of used books) on a First Edition copy of the book A Tale of Two Cities by Charles Dickens, the following would be used;
INSERT INTO used_books (author, title, edition) VALUES
('Charles Dickens', 'A Tale of Two Cities', 'First Edition');
Row contents will be as follows;
+---+---+---+
| author | title | edition | +---+---+---+
| Charles Dickens | A Tale of Two Cities | First Edition | +---+---+---+
The first syntax for INSERT uses separate column and value lists following the name of the table into which the record needs to be added. The number of columns and values must be the same.
The following statement uses a slightly different syntax in order to create 3 new rows in the people table simultaneously (with id set to 31, name set to 'Bruce, and age set to 49 for the first record, etc.);
INSERT INTO people (id,name,age)
VALUES(31,'Bruce',49), (04,'Avery',21), (11,'Mackenzie',17);
Note: All column values must be enclosed in single quotes (for string and temporal data types) in the INSERT statement.
Row contents will be as follows;
+---+---+
9.2.1 INSERT with SELECT
The SELECT command can also be used with insert in order to populate an existing table with matching column data from another table. In this case, we have created a table like City and called it City2, then added all the column/row data from the original City table in to this new table;
INSERT INTO City2 SELECT * FROM City;
The resulting rows will be the same as those of the original City table.
9.2.2 INSERT with LAST_INSERT_ID
The MySQL-specific option LAST_INSERT_ID(expr) can be used after an INSERT to retrieve the last AUTO_INCREMENT value after the insert takes place;
mysql> INSERT INTO City (name, countrycode) -> VALUES ('Sarah City', 'USA');
Query OK, 1 row affected (#.## sec) mysql> SELECT LAST_INSERT_ID();
+---+
| LAST_INSERT_ID() | +---+
| 4080 | +---+
If a table contains an AUTO_INCREMENT column and INSERT ... UPDATE inserts a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. If the statement updates a row instead, LAST_INSERT_ID() is not meaningful. However, LAST_INSERT_ID(expr) can be used to worked around this.
9.2.3 INSERT with NULL Values
In general, if a column has no default value, the effect of omitting it from the INSERT depends on whether it can take NULL values and SQL mode being used:
If the column can take NULL values, it is set to NULL.
If the column cannot take NULL values, it is set to the implicit default for the column data type if strict SQL mode is not enabled. If the strict mode is enabled, an error occurs.
To get the next available number in an AUTO_INCREMENT column, leave the column out of the column list, or give it the value of NULL in the value list. The same applies to TIMESTAMP type columns.
Instructor Notes: Mention that the INSERT/SET example is non-standard.
A column list can also be provided with INSERT/SELECT:
CREATE TABLE districts (id int unsigned auto_increment primary key, district 148
149
150
9.3 The UPDATE Statement
The UPDATE statement modifies the contents of the existing records. To use it, name the table needing to be updated, provide a SET clause that lists one or more column value assignments, and optionally specify a WHERE clause that identifies which records to update. The Update statement uses the following general syntax;
UPDATE table_name SET column=expression(s) WHERE condition [options];
For example, to update the Country table by doubling the Population column for every country, the following statement would be used;
mysql> UPDATE Country
-> SET Population = Population * 2;
Query OK, 232 rows affected, 1 warning (#.## sec) Rows matched: 239 Changed: 232 Warnings: 1
Note: The above statement returns a warning and completes the update for only most of the rows, but not all. This is because some of the updated numbers exceed the number of characters allowed for that column.
The effects of column assignments made by an UPDATE are subject to column type constraints, just as they are for an INSERT or REPLACE. By default, if an attempt to update a column to a value that doesn't match the column definition is made, MySQL converts or truncates the value. If strict SQL mode is enabled, the server will be more restrictive about allowing invalid values.
It's possible for an update to have no effect. This can occur under the following conditions:
When it matches no records for updating; due to an empty table or if no records match the WHERE clause.
When it does not actually change any column values (i.e. the value given is the same as the existing value).
With respect to handling of records with unique key values, UPDATE is similar to REPLACE in some ways, but the two aren't equivalent:
UPDATE does nothing if there is no existing record in the table that contains the specified key values. REPLACE does not require an existing record with the key values and adds one if none exists.
UPDATE can be used to change some columns in an existing record while leaving others unchanged.
REPLACE entirely discards the existing record. To achieve the effect of leaving some columns unchanged with REPLACE, the new record must specify the same values in those columns that the existing record has.
151
152
UPDATE by default makes no guarantee about the order in which rows are updated. This can sometimes result in problems. Suppose that the people table contains two rows, where id is a primary key:
mysql> SELECT * FROM people;
+---+
To renumber the id values to being at 1, the following UPDATE statement:
UPDATE people SET id=id-1;
Row contents will be as follows;
+---+
The statement succeeds if it updates id values first by setting 2 to 1 and then 3 to 2. However, it fails if it first tries to set 3 to 2. That would result in two records having an id value of 2, so a duplicate-key violation occurs. To solve this problem, add an ORDER BY clause to cause the row updates to occur in a particular order:
UPDATE people SET id=id-1 ORDER BY id;
Row contents will be as follows;
+---+
9.3.3 UPDATE with LIMIT
UPDATE also allows a LIMIT clause, which places a limit on the number of records updated. For example, if there are two identical people records with the name value of 'Victor' and there is need to change just one of them to 'Vic', use this statement:
UPDATE people SET name='Vic' WHERE name='Victor' LIMIT 1;
Note: ORDER BY and LIMIT may be used together in the same UPDATE statement.
Row contents will be as follows;
+---+
9.4 The REPLACE Statement
The MySQL-specific statement REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. REPLACE is a MySQL extension to the SQL standard. It either inserts, or deletes and inserts. Uses the following general syntax;
REPLACE INTO table_name (column_list) VALUES(value_list);
In this example, we are replacing a current row of data (containing three columns) in the people database;
REPLACE INTO people (id,name,age) VALUES(12,'Bruce',25);
Note that unless the table has a PRIMARY KEY or a UNIQUE index, using a REPLACE statement makes no sense. It becomes equivalent to INSERT, because there is no index to be used to determine whether a new row duplicates another.
Values for all columns are taken from the values specified in the REPLACE statement. Any missing columns are set to their default values, just as happens for INSERT. It is not possible to refer to values from the current row and use them in the new row.
The REPLACE statement returns a count to indicate the number of rows affected. This is the sum of the rows deleted and inserted. If the count is 1 for a single-row REPLACE, a row was inserted and no rows were deleted. If the count is greater than 1, one or more old rows were deleted before the new row was inserted. It is possible for a single row to replace more than one old row if the table contains multiple unique indexes and the new row duplicates values for different old rows in different unique indexes.
The affected-rows count makes it easy to determine whether REPLACE only added a row or whether it also replaced any rows: Check whether the count is 1 (added) or greater (replaced).
Currently, it is not possible to perform a replace into a table and select from the same table in a subquery.
MySQL uses the following algorithm for REPLACE:
1. Try to insert the new row into the table
2. While the insertion fails because a duplicate-key error occurs for a primary key or unique index:
a. Delete from the table the conflicting row that has the duplicate key value b. Try again to insert the new row into the table
155
156
9.4.1 INSERT with ON DUPLICATE KEY UPDATE Instead of REPLACE
If the MySQL-specific option ON DUPLICATE KEY UPDATE is specified, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. ON DUPLICATE KEY is like REPLACE but “nicer” in some situations because it doesn't throw out the original row. For example, if a column is declared as UNIQUE and contains the value 1, the following two statements have identical effect:
INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
UPDATE table SET c=c+1 WHERE a=1;
The rows-affected value is 1 if the row is inserted as a new record and 2 if an existing record is updated.
If column b is also unique, the INSERT is equivalent to this UPDATE statement instead:
UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
If a=1 OR b=2 matches several rows, only one row is updated. In general, avoid using an ON DUPLICATE KEY clause on tables with multiple unique indexes.
From the previous example in the INSERT section, suppose that id is the AUTO_INCREMENT column.
To make LAST_INSERT_ID() meaningful for updates, insert rows as follows:
INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;
157
9.5 The DELETE Statement
To remove records(data) from tables, use the DELETE command. The DELETE statement allows a WHERE clause that identifies which records to remove. To empty a table entirely by deleting all its records, use the following syntax:
DELETE FROM table_name;
To remove only specific records in a table, a DELETE statement needs to be issued with a WHERE clause that identifies which records to remove:
DELETE FROM table_name WHERE …;
Note: There is no need to mention any columns since it will delete whole rows at a time.
Using DELETE with ORDER BY and LIMIT
DELETE supports ORDER BY and LIMIT clauses, which provide finer control over the way records are deleted. For example, LIMIT can be useful to remove only some instances of a given set of records.
Suppose that the people table contains five records where the name column equals 'Emily'. To keep only one such record, use the following statement to remove four of the duplicated records.
DELETE FROM people WHERE name='Emily' LIMIT 4;
Normally, MySQL makes no guarantees about which four of the five records selected by the WHERE clause it will delete. An ORDER BY clause in conjunction with LIMIT provides better control. For example, to delete four of the records containing 'Emily' but leave the one with the lowest id value, use ORDER BY and LIMIT together as follows;
DELETE FROM people WHERE name='Emily' ORDER BY id DESC LIMIT 4;
Note: The DELETE result will indicate number of rows affected, which can be zero (0) if the statement did not cause a change to be made.
158
Lab 9-A
In this exercise you will use some of the DML statements presented in this chapter to manipulate the data contained in the tables. This will require a MySQL command line client and access to the mysql server.
ACTION (You Do) COMPUTER RESPONSE / Comment
1. After logging into the mysql client, use the test