Modifying Data in Your Tables

Although we have seen how to insert new data into our tables and how to retrieve it, there will be times when we will want to modify an existing row (or set of rows) in our table. For example, a user decides to change her name after she gets married, somebody else moves and needs to change his address in an address table, or we want to mark an order as cancelled.

We will use the UPDATE query to modify existing data in our tables. Its basic syntax is as follows:

 UPDATE TableName   SET ColumnName1 = NewValue1, ColumnName2 = NewValue2, ...   WHERE SomeColumn operator value; 

For example, if we had a user named Petunia Durbin in our Users table who wanted to change her last name to Wayworth, we could execute the following query:

 UPDATE Users   SET full_name = 'Petunia Wayworth'   WHERE user_name = 'pdurbin'; 

If she wanted to change both her username and full name, we could include multiple values to update in the SET portion of the query by separating them with commas:

 UPDATE Users   SET full_name = 'Petunia Wayworth', user_name = 'pwayworth'   WHERE user_name = 'pdurbin'; 

Be careful when specifying a WHERE clause for an UPDATE query. A simple slipup in your query could cause a painful and unintended mass update in the database.

The currently connected user needs to have the UPDATE privilege to perform this action.