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

Basics Creating a Document Head and Body Elements Formatting Text Creating Lists Embedding Images URLs Explained Linking to Documents Creating Tables Forms Form Basics Input and Textarea Select Lists Frames Framesets The Frame Element Nested Framesets Targetting Frames Inline Frames		Tips and Tricks Meta Tags Transition Effects HTML Generators Create a Document Create a List Create a Table

Basics Implementing CSS CSS Syntax Pseudo Classes/Elements CSS Classes CSS Properties Font Properties Color and Background Text Properties Border Properties Margins and Padding Size and Position		Tips and Tricks Menu Buttons Special Effects

Basics Running a Script Variables Expressions and Operators Objects.Properties.Methods The Date Object Strings Regular Expressions Defining RegExp Patterns Branches and Conditions Loops Arrays Array Basics Array Methods Sorting Arrays User-Defined Functions Cookies Windows Frames		Tips and Tricks Image Replacement Using Includes Form Validation Debugging

Basics Creating a Script Running a Script Variables Expressions and Operators Strings Strings Basics Strings and Substrings Replacing Substrings Regular Expressions Branches and Conditions Loops Arrays Array Basics Array Functions Sorting Arrays User-Defined Functions Include and Require Uploading Files File Functions Session Variables		Tips and Tricks Page Templates Form Reply Scripts Form Validation JavaScript to PHP