Modify A Single Row
Often you will need to modify the data in a row of a MySQL database table. While you can delete the row and insert a replacement, it is often easier to update the row in place. You can use the UPDATE query in MySQL to modify the values of one or more columns in one or more rows.
For a basic UPDATE query, you simply specify the table name to update and use the SET keyword to name one or more columns, and what one value you want to put in all the fields in that column:
SYNTAX of UPDATE
UPDATE tableName
SET columnName = newValue;
For example, this query sets every field in the city column in the mail table to Salt Lake City:
Example of UPDATE
UPDATE mail_table
SET city="Salt Lake City";
As with other MySQL queries, UPDATE will affect all of the table's rows unless you specify otherwise (with a WHERE clause) - so unless all of the members of the database are really in the same city, you wouldn't use the above query, because it would change the city field in every row and potentially cause the loss of correct data. Thus, like DELETE and DROP, use this command with caution.
To specify which rows to modify, you add the WHERE clause to the UPDATE query.
SYNTAX of UPDATE with WHERE
UPDATE tableName
SET columnName = newValue
WHERE columnName = certainValue;
It searches all the fields in that second column, and in any row where it finds a value that matches certainValue, it sets the field in the first column name to the newValue. For example this query limits the city update to rows of the mail table where the person's name is John Smith:
Example of UPDATE with WHERE
UPDATE mail_table
SET city="Salt Lake City"
WHERE name="John Smith";
If the column mentioned in the WHERE clause (name column) is a primary key and has a unique value for each row, this ensures that only a single row will get updated. If you specify a WHERE clause that matches more than one row, all rows that match will be changed.
Modify Multiple Rows
If you specify a WHERE clause that matches more than one row in an UPDATE query, all of the matching rows will be modified. This is useful when you need to make a global change to a group of rows in a table.
For example, the following query updates the mail table. It looks for a value of "pgh" in fields in the city column and assigns the value "Pittsburgh" to fields in the column instead.
UPDATE mail_table SET city="Pittsburgh"
WHERE city ="pgh" ;
There is a special way of checking for a NULL value. You cannot use the standard = sign to check for a NULL value, because the NULL value really means that the value is not defined at all. Instead, MySQL provides the IS NULL keywords, which allow you to test for a NULL value. For example, the following query looks for a NULL value in fields in the city column and assigns the value "Unknown" to fields in the column instead:
UPDATE mail_table SET city="Unknown"
WHERE city IS NULL;
You can use any column name in the WHERE clause. What if you want to check for values in multiple columns? You can also use the AND and OR keywords to combine multiple WHERE conditions.
SYNTAX of UPDATE with MULTIPLE-CONDITION WHERE
UPDATE tableName
SET columnName = newValue
WHERE oneCondition AND anotherCondition;
UPDATE tableName
SET columnName = newValue
WHERE oneCondition OR anotherCondition;
If we use OR, either condition must be true for the change to be made. If we use AND, both conditions must be true for the change to be made. The following query updates the city field to "Pittsburgh" only if the record has both a first name of "John" and a last name of "Smith". It would not update if either name were different (Peggy Smith or John Doe would not get updated):
Example of UPDATE with MULTIPLE-CONDITION WHERE
UPDATE mail_table
SET city="Pittsburgh"
WHERE fname = "John" AND lname ="Smith";
The following query checks for a NULL value or a blank value (represented by two quotes with nothing in between) and updates occurrences of either one:
Example of UPDATE with MULTIPLE-CONDITION WHERE
UPDATE mail_table
SET city="Unknown"
WHERE city IS NULL OR city ="";
After you perform an UPDATE that affects one or more rows, MySQL displays the number of rows that were affected. If no rows were affected, it does not mean your query was incorrect, just that no rows matched the WHERE clause.
Because an UPDATE query that affects a large number of rows can take a long time, you can use the LOW_PRIORITY options with UPDATE. If this option is specified, the MySQL server will wait until no clients are accessing the table before performing the update. This reduces the impact of the query on other users, but will increase the length of time the update takes when the table is busy.
For example, the following query uses the LOW_PRIORITY keyword and updates the city column as in the previous example:
Example of PRIORITIZED UPDATE
UPDATE LOW_PRIORITY mail_table
SET city="Unknown"
WHERE city is NULL;
Update All Table Rows
If you omit the WHERE clause from an UPDATE query, it will affect all of the rows of the table.
SYNTAX of UPDATE
UPDATE tableName
SET columnName = newValue;
For example, this query sets the address field in every row of the mail table to the same value for all rows:
Example of UPDATE
UPDATE mail_table
SET address = "32 South E Street";
In a simple example like this, the UPDATE query replaces all existing values in all rows for the column with the new value. If done by accident, this causes the loss of some unique data, and is therefore only useful in rare cases. For example, you may find it useful if you are adding a new column to the table or deciding on a new purpose for an existing column, and want to start with a default value for all rows.
Limit Updated Rows
You can add the LIMIT clause to an UPDATE query to limit the number of rows that can be updated. For example, this query updates only the first three rows:
Example of UPDATE with LIMIT
UPDATE mail_table
SET postal = 33422 LIMIT 3;
Although this example does not include a WHERE clause to control the rows to be updated, the LIMIT clause specifies that only a maximum of three rows will be updated. You cannot specify the order of the UPDATE query, so you cannot control which rows will updated.
The LIMIT clause is useful for two purposes. First, you can use it as a safeguard either with or without the WHERE clause to prevent an incorrect query from damaging data. If your WHERE clause unexpectedly matches more records than you expected, this can prevent the loss of too much existing data. The following example uses LIMIT with a WHERE clause:
Example of UPDATE with LIMIT and WHERE
UPDATE mail_table
SET postal = 33422
WHERE postal = 33455 LIMIT 1;
The second use for the LIMIT clause is to minimize the slowdown of the MySQL server. If you are performing a complicated UPDATE query on a large table, it can slow down the table for other clients, and may take minutes or even hours depending on the size of the table and the speed of the server.
If you specify a number in the LIMIT clause, you can perform only part of the update and test the server's response or repeat the same query later to update more rows of the table.
Update Multiple Columns
You can use multiple assignment statements after the SET keyword within an UPDATE query to update multiple columns for each row being updated. To update multiple columns, simply separate the assignments with a comma.
SYNTAX of UPDATE MULTIPLE COLUMNS
UPDATE tableName
SET columnName = newValue, anotherColumnName = newValue;
The following statement would affect the fields of 3 columns in all the rows of the mail table:
Example of UPDATE MULTIPLE COLUMNS
UPDATE mail_table
SET address="5 Maple St.", city="Pittsburgh", state="PA";
Of course, adding a WHERE clause will help you change some rows rather than all. For example, suppose you are working with the mailing list in the mail table again. For any listing that does not have a value in the postal code column, you want to change the address, city, and state columns to blanks. You can do this with the following UPDATE query:
UPDATE mail_table
SET address="", city="", state=""
WHERE postal IS NULL;
This example includes three assignments after the SET keyword, assigning blank values to the address, city, and state columns. The WHERE clause looks for any row that has a NULL value in the postal column.
When you specify multiple assignments, the MySQL server evaluates them from left to right for each row. While this makes no difference in the example above, it can affect some queries. For example, if you assign two different values to the same column in the UPDATE query, the one you specify last will be the final value of the column.
Because a query like this can cause data loss, be careful when entering it and verify the syntax before you execute the query. For example, if you inadvertently added a semicolon at the end of the second line of this query, it would execute immediately without the WHERE clause, erasing the address, city, and state of all rows in the entire table.
Copy columns
You can use the value of any existing column of the table within an UPDATE query. This allows you to update one column based on the value of one or more other columns.
SYNTAX of UPDATE that COPIES COLUMN VALUES
UPDATE tableName
SET columnToPutDataInto =columnToGetDataFrom;
For example, this query copies each row's lastname data into that row's familyname field:
Example of UPDATE that COPIES COLUMN VALUES
UPDATE mail_table
SET familyname = lastname;
You can also set many columns at a time. For example, suppose you were to add columns for a second address to the mail table-- assuming there's already an address column, we want to add an address2 column, etc. Expanding on what we know from previous lessons, the following ALTER TABLE query would add the 4 new columns to the table:
ALTER TABLE mail
ADD COLUMN (
address2 VARCHAR(120),
city2 VARCHAR(50),
state2 CHAR(2),
postal2 VARCHAR(5)
);
Using this UPDATE query, you could then copy what's in the field of the current address column for each row, inserting it into the field of that row's address2 column, then do the same to fill the other new columns:
Example of UPDATE from COLUMN VALUES
UPDATE mail_table
SET address2=address,
city2=city,
state2=state,
postal2=postal;
This query uses the existing values in the address, city, state, and postal columns as the values of the new address columns. Because no WHERE clause is specified, this query affects all of the rows of the table. You can also use a WHERE clause to update only certain rows. The following example updates only rows where the state field has the value 'CA':
UPDATE mail_table
SET address2=address,
city2=city,
state2=state,
postal2=postal
WHERE state='CA';
You can use any of the existing values from the table's columns in this way in an UPDATE query.
You can also use a variety of MySQL functions to modify or combine the values of one or more columns to form the value of a column. You might use an UPDATE query without the WHERE clause to copy data from one column to itself when you combine it with one or more MySQL functions. For example, the UPPER function changes all of the letters of a text string to uppercase. You can use this function to change the data in a column to all uppercase:
SYNTAX of UPDATE with CASE CHANGE
UPDATE tableName
SET columnName = UPPER(columnName);
Example of UPDATE with CASE CHANGE
UPDATE mail_table
SET address=UPPER(address);
Rather than setting every address field to the same value it was before, this version of the query performs a useful purpose. It runs each address through the UPPER function to convert it to the same value in all capital letters, and stores the changed address in the address field. This technique is useful for making a formatting change to a field throughout a table's rows.