Home > MySQL > Insert, Update and Deleteprinter version

MySQL Insert, Update and Delete

This tutorial assumes that you have access to the MySQL command prompt and uses the people table created in the Create Tutorial.

Insert

Inserting data into a table can be achieved in different ways and two of the methods are covered below.

INSERT INTO table_name VALUES ()

With this method the attributes for a table row are listed in parenthesis, separated by commas, and must conform to the following rules.

All attributes must be present in the list.
Attributes must be listed in the same order as they were when the table was created.
All non-numeric attributes must be enclosed in either single or double quotes.
An attribute that uses the auto_increment modifier should be set to NULL

First Method of Inserting Data into a Table

mysql> INSERT INTO people VALUES
(NULL, "Dave", "Clark", "Main Street", "West Lothian", "UK", 40);
Query OK, 1 row affected (0.05 sec)

mysql>

If a string contains single quotes then double quotes can be used to enclose it or vice versa.

Alternatively quotes can be escaped using the \ character as covered in the section on escape sequences in the PHP Strings Tutorial.

INSERT INTO table_name SET

This method doesn't require all the attributes to be listed and those that are can be in any order.

Each attribute to be inserted is listed by name followed by = then the value to be assigned.

Second Method of Inserting Data into a Table

mysql> INSERT INTO people SET
firstname = "Dave",
lastname = "Clark",
address = "Main Street",
county = "West Lothian",
country = "UK",
age = 40;
Query OK, 1 row affected (0.05 sec)

mysql>

Notice that there's no need to mention the id attribute above since it will auto_increment.

Update

Updating an attribute is straightforward and uses syntax similar to the previous INSERT example.

Updating an Attribute

mysql> UPDATE people SET country = "US" WHERE id = 1;
Query OK, 1 row affected (0.05 sec)

mysql>

Note: If the WHERE clause wasn't used above then all country attributes in every row of the table would be updated.

Any attribute can be used in a WHERE clause to selectively update, but only the id attribute guarantees that only one row will be affected (because we made it a primary key and therefore unique).

Delete

The DELETE statement is used to remove rows from a table. Even if all rows are removed from a table, the table itself is not removed - as it would be with the DROP statement.

Deleting a Row

mysql> DELETE FROM people WHERE id = 1;
Query OK, 1 row affected (0.05 sec)

mysql>

Note: As with the UPDATE statement, if the WHERE clause wasn't used above then all rows in the table would be affected i.e. deleted.

Any attribute can be used in a WHERE clause to selectively delete rows, but only the id attribute guarantees that only one row will be affected (because we made it a primary key and therefore unique).

Previous - Show and Describe Next - Querying