Add and Delete Data

Insert and Delete Queries

After you create a database and one or more tables to store data, you can use the INSERT and REPLACE commands in MySQL to add rows of data to the table.

Example of INSERT ROW

INSERT INTO address_table VALUES
         ("John Smith", 28, "Chicago", "IL", 0);

You can add rows by specifying which columns you'll fill, or you can fill all columns. See the next few pages for details.

After a table contains data, you can use the DELETE command to delete a row, a group of rows, or the entire table.

Example of DELETE ROWS

DELETE FROM address_table
         WHERE city = "Chicago";
         

Add a Row to a Table

The primary way to add rows of data to a table is with the INSERT query. You can add a row of data by specifing values for the fields in the same order they were specified when the table was created. When using this type of INSERT command, be sure to specify a value for every column of the table.

SYNTAX of INSERT ROW, specifying values for all columns

INSERT INTO tableName VALUES
          (valueForColumn1, valueForColumn2, valueForColumn3);

Example of INSERT ROW, specifying values for all columns

INSERT INTO address_table VALUES
          ("John Smith", 28, "Chicago", "IL", 0);

Within the VALUES section of the INSERT query, you specify a value for each of the columns of the table.

• Values for text fields should be enclosed within single or double quotes.
• Values for numeric fields can simply be included as a number, with no quotes.
• The entire list of values should be enclosed within parentheses.
• Separate each value from the next value with a comma. Do not put a comma before the closing parenthesis.

As with other SQL queries that work with tables, you should first use the USE command to select the database that contains the table you will be working with. You can insert one row into the table with each INSERT query. Or you can insert multiple rows, by enclosing each row's values with parentheses, and adding a comma between rows:

Example of INSERT MULTIPLE ROWS, specifying values for all columns

INSERT INTO address_table VALUES
           ("John Smith", 28, "Chicago", "IL", 0),
           ("Jane Doe", 24, "Springfield", "IL", 0),
           ("George Spelvin", 38, "New York City", "NY", 0);

You can optionally specify one or more column names and provide values for those columns only If you do not specify column names, you must provide values for all columns in the correct order.

Add Rows, Specifying Columns

In a table with a large number of fields, you may find it cumbersome to specify values for each of the columns in order. Fortunately, you can use an alternate INSERT query syntax to insert a row and specify values for whichever columns you choose, in the order you specify.

To insert a row and specify the columns to add, list the column names in parentheses before the list of values.

SYNTAX of INSERT ROW, specifying values for CERTAIN columns

INSERT INTO tableName
         (column6, column8)
         VALUES
               (valueForColumn6, valueForColumn8);

For example, imagine an address table with twelve columns. This query inserts a row into the address table and specifies values for only the name, age, and address columns:

Example of INSERT ROW, specifying values for CERTAIN columns

INSERT INTO address_table
         (name, age, address)
         VALUES
               ("John Doe", 23, "321 Elm Street");

• Values for text fields should be enclosed within single or double quotes.
• Values for numeric fields can simply be included as a number, with no quotes.
• The entire list of values should be enclosed within parentheses.
• Separate each value from the next value with a comma. Do not put a comma before the closing parenthesis.

And you can insert multiple rows, by enclosing each row's values with parentheses, and adding a comma between rows:

Example of INSERT MULTIPLE ROWS, specifying values for CERTAIN columns

INSERT INTO address_table
         (name, age, address)
         VALUES
               ("John Doe", 23, "321 Elm Street"),
               ("Jane Doe", 24, "Apt 3G, 40 South Ave."),
               ("George Spelvin", 38, "333 S Main St.");

In this form of the INSERT query, you need to use the correct column names to match the table's definition, but you do not need to specify values for all columns or in any particular order. The row added to the table will contain the columns and values you specified. It will also include default values for any columns your INSERT query did not include.

This type of INSERT query has the advantage of being simpler when you are not specifying values for all fields. Another advantage is that if you later change the definition of the table and add columns or change the column order, the same INSERT query will still work as long as the columns you included in the query have not changed.

If you specify a column name that is not defined in the table, MySQL will return an 'Unknown column' error message.

Specify Insert Priority

With some MySQL table types, an INSERT query that adds data to the table will lock the table, and the table cannot be read by other clients during the processing of INSERT. You can optionally specify the LOW_PRIORITY keyword in an INSERT query to allow clients to continue to read the table:

Example of INSERT LOW_PRIORITY

INSERT LOW_PRIORITY INTO address_table VALUES
            ("Jane Smith", "321 ELM Street", "Chicago", "IL", 0);

When you specify LOW_PRIORITY, your client waits until no clients are reading from the table before inserting the row. In a busy table, this may take some time.

The DELAYED option is similar to LOW_PRIORITY. When you specify this keyword, the MySQL client returns immediately, but the server holds the row and inserts it when no clients are reading from the table.

Example of INSERT DELAYED

INSERT DELAYED INTO address_table VALUES
        ("Jane Smith", "321 ELM Street", "Chicago", "IL", 0);

The LOW_PRIORITY and DELAYED options perform similar functions and cannot both be used in the same INSERT query. Be default, neither option is enabled.

Auto-Increment

As another usage of INSERT queries, you can use INSERT to work with a table that includes an auto-increment column. Auto-increment columns are integer columns created with the Auto_Increment attribute. They are automatically filled with a unique numeric value when you create a row.

To insert a row in a table with an auto-increment column, simple use a 0 (zero) or NULL as the value for that column:

SYNTAX of INSERT into AUTO INCREMENT

INSERT INTO tableName (nameOfAutoIncrementColumn) VALUES
         (NULL);

The result is that the database inserts a numeric value that has not yet been used in that column. This saves you the trouble of figuring out which number hasn't been used.

For example, this INSERT query adds a row to the "links" table which includes values for the "title" and "link" fields as well as the auto-increment "link_ID" field.

Example of INSERT into AUTO INCREMENT

INSERT INTO links_table (title, link, link_ID) VALUES
          ("Yahoo", "http://www.yahoo.com/", 0);

Timestamp

Timestamp columns store a date and time to the exact second. If a table includes a timestamp column, it will usually be updated with the current time and date when you add a row using INSERT. Only the first timestamp column in a table is automatically updated.

To add a row and ensure that the timestamp column is updated, specify the NULL value for the timestamp column.

SYNTAX of INSERT into TIMESTAMP

INSERT INTO tableName (nameOfTimestampColumn) VALUES
         (NULL);

This example inserts a row into the address table and updates the timestamp in the updatetime column:

Example of INSERT into TIMESTAMP

INSERT INTO address_table (name, updatetime) VALUES
        ("Einstein", NULL);

The NULL value updates the timestamp column with the current date and time. Unlike auto-increment columns, you cannot use zero to force an update.

If for some reason you want to use a different value in a timestamp, you can override it by specifying a date and time value. 20090822104533 is an example of an acceptable timestamp value; it means August 22, 2009, 10:45:33am.

Replace an Existing Row

The replace command is identical to INSERT with the exception that if you add a row that duplicates the values of an existing row in a unique index or primary key column, the existing row is deleted and replaced with the new row.

SYNTAX of REPLACE

REPLACE INTO tableName (columnName1, columnName2) VALUES
         (valueForColumn1, valueForColumn2);

For example, the following command adds a row to the mail table, which has a Primary Key called "userID". If an existing row already has a userID value of 1, this replaces that row; otherwise a new row is created.

Example of REPLACE

REPLACE INTO mail_table
            (name, address, userID)
            VALUES
               ("John Smith", "321 Elm Street", 1);

Insert Rows From Another Table

You can use SELECT with INSERT to select one or more columns of data in one or more rows of an existing table, then copy that data to the destination table. The SELECT clause can specify column names and the table to take data from.

SYNTAX of INSERT... SELECT

INSERT INTO table1Name (destinationColumn1, destinationColumn2)
         SELECT sourceColumn1, sourceColumn2 FROM table2Name ;

Example of INSERT... SELECT

INSERT INTO mail_table (mail_name_column, mail_ address_column)
         SELECT name_column, address_column FROM address_table;

If all the field (column) names for two tables are the same, you can use a wildcard (*) to copy all of the fields:

Example of INSERT... SELECT using wildcard

INSERT INTO mail_table
         SELECT * FROM address_table;

With this syntax, all of the columns will be copied if there is a column with the same name in the destination table. If a column does not exist in the destination table, the other columns are still copied and a warning message is displayed.

You can also use an optional WHERE clause to specify one or more conditions that each row must match in order to be copied.

Delete a Specific Row

If you need to remove one or more rows of data from a table, you can use a DELETE query in MySQL. To use DELETE, you specify the table from which you want to delete rows. You use the WHERE clause to specify one or more records to delete. WHERE usually mentions a column name and a value. It goes through the whole table, checking that column for any rows where the field matches the given value, and ONLY deletes those rows.

SYNTAX of DELETE SPECIFIC ROWS

DELETE FROM tableName
         WHERE columnName = certainValue;

For example, we could delete only those rows where the zip code is 15218:

Example of DELETE SPECIFIC ROWS

DELETE FROM address
         WHERE zip_code_column = 15218;

Instead of an equal sign, you can use 'greater than' (>) or 'less than' (<). This would enable us to delete only those rows where the age is greater than 20:

Another example of DELETE SPECIFIC ROWS

DELETE FROM cool_people_table
         WHERE age_column > 20;

DELETE FROM cool_people_table
         WHERE person_ID = 6;

Delete all Table Rows

You can use the DELETE command from MySQL to delete one or more rows of data from a table. The simplest version of a DELETE query deletes all of the rows from a table. Be cautious because this command erases the entire table!

SYNTAX of DELETE ALL ROWS

DELETE FROM tableName;

The TRUNCATE query is identical to DELETE except that it does not allow a WHERE clause. It deletes all of the table's records without confirmation. When you delete all rows using TRUNCATE or DELETE, MySQL actually deletes the entire table and then creates a new, empty table. This improves speed, but there is no way to determine the number of rows that were deleted by the query.

SYNTAX of TRUNCATE ALL ROWS

TRUNCATE TABLE tableName;

Delete a Limited Number of Rows

You can optionally use the LIMIT clause with the DELETE query to limit the number of rows to be deleted. This serves two purposes: first, if you are unsure how many rows will be matched by a WHERE clause, using a LIMIT clause will ensure that a large number of rows cannot be deleted by mistake.

Second, it limits the amount of time a table is locked during the DELETE process. You can minimize the slowdown caused by a DELETE query by using a LIMIT clause and repeating the DELETE command until all of the desired rows have been deleted.

SYNTAX of DELETE a LIMITED NUMBER of ROWS

DELETE FROM tableName LIMIT numberOfRowsToDelete;

The next example would delete the first 10 rows of the "address" table:

Example of DELETE a LIMITED NUMBER of ROWS

DELETE FROM address_table LIMIT 10;

This can be combined very effectively with a WHERE clause (see the "Delete a Specific Row" page). The next example would search the "address" table for the first 4 rows where the value in the "state" column is "CA", and delete those rows:

Example of LIMIT with WHERE

DELETE FROM address_table
        WHERE state = "CA" LIMIT 4;

Order By

You can use the ORDER BY clause along with LIMIT in a DELETE query. This allows you to control not only how many rows are deleted, but which rows are deleted. It's as if MySQL considers all the rows in alphabetical or numeric order based on the column you select, then deletes in that order. (It doesn't actually rearrange the order of the rows in the table.)

The next example would search the rows of the "address" table as if they were sorted according to the values in their "age_column" column, and delete the 2 rows that come first numerically (in other words, it would delete the two youngest):

SYNTAX of LIMIT with ORDER BY

DELETE FROM address_table
       ORDER BY age_column LIMIT 2;

You can optionally follow the ORDER BY clause with the keyword ASC to delete rows in ascending order, which means alphabetically A-Z or numerically 1, 2, 3, etc. DESC means the opposite of that-- in descending order. ASC is the default, meaning if you say ORDER BY but don't say ASC or DESC, it sorts in ascending order anyway.

The next example would search the rows of the "address" table as if they were sorted according to the values in their "name_column" column in REVERSE alphabetical order, and delete the first 34 rows in the list (those beginning with Z, then Y, then X, etc, until 34 are gone):

SYNTAX of LIMIT with ORDER BY

DELETE FROM address_table
       ORDER BY name_column DESC LIMIT 34;

Delete Data by Date

When you include a timestamp field in a table, it is automatically updated with the current date and time when each row is added to the table. If you are using a table to store data that becomes less useful as it gets older, you can use a timestamp field with a DELETE query to delete all of the rows that were created or updated before a certain date.

For example, the addess table has a timestamp column called updatetime_column. You can use a DELETE query with a WHERE clause to compare the values to a recent date, and delete older data from the table. The following example deletes all rows that were created or updated before January 31st, 2001:

Basic delete by date SYNTAX

DELETE FROM address_table
         WHERE updatetime_column < 20010131000000;

This type of DELETE command is especially useful with tables that are used to log events. For example, you may be using a table to log an entry for each user that visits a web page. On a busy site, this table will quickly become large and unwieldy. You can use a DELETE query regularly to delete all of the rows that are older than you need.