INSERT - Single-row insertion with SET clause

Use this variant of the MySQL statement, INSERT to add only one row of data into a MySQL table per statement. With the SET clause, one or more columns names are listed, each followed by the value to which each column is to be set.


Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

  [INTO] table
  SET column={expression|DEFAULT}, ...
  [ON DUPLICATE KEY UPDATE column=expression, ... ]


This variant of the MySQL statement, INSERT allows only one row of data to be inserted into a table per MySQL statement. The SET clause lists one or more columns names, each followed by the value to which it is to be set. The value given can be a static value or an expression.


SET client_name =  'Geoffrey & Company',
city = 'Boston', state = 'MA';

This example of this syntax of the MySQL statement INSERT lists three columns along with the values to be set in a row entry in the clients table. Other columns in the newly inserted row will be handled in a default manner. For instance, an AUTO_INCREMENT column will be set to the next number in sequence.

As mentioned earlier, the ON DUPLICATE KEY UPDATE clause allows an INSERT statement to handle rows that already contain specified values. An example follows:

ON clients(client_name,telephone);

ALTER TABLE clients 
ADD COLUMN new_telephone TINYINT(1) 
AFTER telephone;

SET client_name = 'Marie & Associates',
new_telephone = 0
telephone = '504-486-1234'
new_client = 1;

This example starts by creating an index on the client_phone column in the clients table. The index type is UNIQUE, which means that duplicate values for the combination of client_name and telephone columns are not allowed. With the second MySQL statement, we add a column to flag new telephone numbers for existing clients. The INSERT statement tries to insert the specified client name and telephone number. But it indicates that if there is already a row in the table for the client, a new row is not to be added. Instead, the existing row is to be updated per the UPDATE clause, setting the original entry's telephone column to the value given in the SET clause. The assumption is that the new data being inserted either is for a new client or is an update to the existing client's telephone number. Instead of using a column value after the equals sign, a literal value or an expression may be given.