Five Handy Tips for MySQL’s Powerful UPDATE Statement

In the MySQL INSERT Statement Variations article, we explored the use of MySQL INSERT…SELECT syntax and other ways to construct an effective INSERT statement. Today we will be shifting our focus to the UPDATE statement, which is used to modify existing records in a table. Among its many features, we’ll be looking at how to update multiple tables, avoiding mistakes, and how to limit how many rows are updated.

Basic Syntax

Here is the basic syntax of the UPDATE statement that we learned in the “MySQL INSERT Statement Variations” article:

UPDATE table_name
SET column1=value, 
    column2=value2,
...
WHERE some_column=some_value

As an example, we used the UPDATE statement to change one a female employees’ last name after marriage. Notice that we used the id field, as it is unique, and NOT Kirsten’s maiden name, which is not guaranteed to be unique. The following SQL code was used:

MySQL>UPDATE employees
-->SET name='Kirsten Johnson'
-->WHERE id=3;

Batch Updates

As mentioned above, careful use of the where clause is essential for avoiding destructive changes to your table(s). However, there are times that you may want to affect table values on a global scale.

Take the following case scenario for instance. For security reasons, we are told to disable all of the accounts in the users table. All that’s required is to set the active_indicator field to zero (0):

MySQL>UPDATE valid_users
-->SET active_indicator=0;

That will update all of the rows in the table.

Updating Multiple Tables

We can apply the update on an even larger scale by adding tables to the tables list, much as in a SELECT statement. Here’s a statement that will affect several tables:

MySQL>UPDATE valid_users,
-->          clients,
-->          stores
-->SET active_indicator=0;

You can add a WHERE clause to the multiple table UPDATE just like the single table one, but be careful that you don’t refer to any columns that don’t exist in all of the named tables. The following statement would presumably fail because cities don’t usually have phone numbers:

MySQL>UPDATE valid_users,
-->          clients,
-->          stores,
-->          cities
-->SET active_indicator=0
-->WHERE phone_number IS NULL or phone_number = '';

Failing Gracefully

Speaking of failing, what happens when an UPDATE encounters an error? As a rule of thumb, SQL statements either succeed or fail. It’s all very black and white. If there is one field that can’t be set, then none are. The following code is attempting to clobber an existing primary key id and will therefore fail, leaving both the bundle and id fields intact:

MySQL>UPDATE resource_bundle
-->SET bundle = 'label bundle',
-->    id = 1    
-->WHERE id = 11;

Including the IGNORE keyword tells the UPDATE statement to not abort even if errors occur during the update. Rows for which duplicate-key conflicts would occur, like the example above, are not updated. That means that other fields, such as the bundle, would not be updated where a primary key conflict exists. However, rows for which columns would be updated to values that would cause data conversion errors are updated to the closest valid values instead. Here’s an update that fails because the operator_licence field was assigned a type of INT, which is too small to hold the 15 digit value:

MySQL>UPDATE transportation_company
-->SET operator_licence = 123456789012345   
-->WHERE id = 2000;   
 
SQL ERROR: Out of range value for column 'operator_licence' at row 1

Adding the IGNORE qualifier to the statement gets rid of the error, but truncates the field to 10 digits in order to accommodate the value:

MySQL>UPDATE IGNORE transportation_company
-->SET operator_licence = 123456789012345   
-->WHERE id = 2000;   

The last field on the right is the one that was updated. This is not what we intended!

For this reason, the IGNORE qualifier is not a thing to be taken lightly. Tread carefully if you must use it!

Avoiding Catastrophe

There’s always an element of uncertainty when using a WHERE clause. It’s all too easy to make a small but costly mistake. In a world where data is as valuable as gold, there’s no room for a cavalier attitude in database administration! To limit the possibility of data corruption, always make sure that you back up all of the named tables before attempting to execute any updates that contain a WHERE clause. (You perform regular backups right?) Another step that you can take is to perform a regular SELECT on the data first to see exactly which rows will be affected. For example, here’s an UPDATE that contains a complex WHERE clause:

UPDATE PRODSALES
SET TSN = TSNCUST.TSN
WHERE CUSTNO in
(SELECT CUSTNO FROM TSNCUST WHERE
TSNCUST.CUSTNO = PRODSALES.CUSTNO AND
TSNCUST.PRODLINE = PRODSALES.PRODLINE AND
PRODSALES.SALEDATE >= TSNCUST.STARTDATE AND
PRODSALES.SALEDATE <= TSNCUST.ENDDATE)
AND PRODSALES.RANKNO BETWEEN MIN(PRODSALES.YEARTOTAL) AND MAX(PRODSALES.YEARTOTAL)
AND YEAR BETWEEN 2000 AND 2010

We can create a SELECT statement to see which rows we will be updating with only minor changes to the code:

SELECT TSN
FROM   TSNCUST
WHERE  CUSTNO in
(SELECT CUSTNO FROM TSNCUST WHERE
TSNCUST.CUSTNO = PRODSALES.CUSTNO AND
TSNCUST.PRODLINE = PRODSALES.PRODLINE AND
PRODSALES.SALEDATE >= TSNCUST.STARTDATE AND
PRODSALES.SALEDATE <= TSNCUST.ENDDATE)
AND PRODSALES.RANKNO BETWEEN MIN(PRODSALES.YEARTOTAL) AND MAX(PRODSALES.YEARTOTAL)
AND YEAR BETWEEN 2000 AND 2010

After executing the UPDATE, MYSQL will tell you how many rows were updated. If the number does not match what you were expecting, it may mean that you have to make some changes to your statement or go back to the drawing board:

Query OK, 1020 rows affected (0.29 sec)

Limiting the Number and Order of Rows Affected

In addition to including a WHERE clause, you can further limit the scope of the UPDATE by adding the LIMIT row_count qualifier. It causes the statement to stop as soon as it has reached the number of rows that satisfy the WHERE clause, whether or not they actually were changed.

The UPDATE statement also supports the ORDER BY clause, which allows you to control the order in which rows are affected. While many people associate the ORDER BY with the WHERE clause, it is actually a distinct and separate operator. Thus, it works well in batch updates:

MySQL>UPDATE valid_users,
-->SET active_indicator=0
-->ORDER BY LAST_NAME,
-->         FIRST_NAME

Used in conjunction with the LIMIT clause, the rows will be updated in the order specified by the clause until the LIMIT count has been reached:

MySQL>UPDATE valid_users,
-->SET active_indicator=0
-->ORDER BY LAST_NAME,
-->         FIRST_NAME
-->LIMIT 100

Unfortunately, you cannot use ORDER BY or LIMIT with a multiple-table UPDATE.

We covered a lot of ground today, but there are a few other features of the UPDATE statement that weren’t touched upon here. I would encourage you to check out the MySQL docs for your version. They are available for download in several formats on their developer site.

Additional Resources

MySQL Reference Manual – 12.2.11. UPDATE Syntax

» See All Articles by Columnist Rob Gravelle