SQL UPDATE Statement: Editing Data in Your Database

Structured Query Language (SQL) is an industry-standard programming language that provides the essential tools you need to search databases and return the necessary results. SQL makes it possible to make changes to the data a company stores in a relational database and keep it current with the UPDATE statement.

An SQL UPDATE statement modifies records in a table or multiple tables of your database to reflect the newest available version of the data. An UPDATE statement changes the desired records already in the database, but if not used properly, it’s possible to accidentally overwrite records and lose existing data. Without a backup, your data could be corrupted or completely lost, so it’s wise to maintain a backup of your current database at all times, especially when you are learning new commands like UPDATE.

Writing a Basic SQL UPDATE Statement

Like other statements in SQL, UPDATE statements are not complex, but it’s important to understand the basic format before applying an UPDATE statement to multiple records. Here is the basic syntax:

UPDATE table_name SET column_name = value;

Here is an example database table called customers:

At the very minimum, an SQL UPDATE statement looks something like this:

UPDATE customers SET first_name= ‘Jack’;

Here, the UPDATE statement sets the first_name column of all the records in the customer table to “Jack.” The statement first identifies the table you want to change, which is customer. Then, it specifies the column you wish to update: first_name. Finally, the statement defines the new value “Jack” that will replace any current values in the column.

Although this is a valid SQL statement, it’s not a typical UPDATE query, and it’s most likely not how you want to make changes to essential database values. As it appears here, this statement changes all records in the specified column firast_name to “Jack.” That kind of global change can be disastrous, forcing you to recover previous versions with a backup. After all, some of the customers would prefer using their own first name.

Adding the WHERE Clause to an UPDATE Statement

The SQL WHERE clause can prevent a global change like the above example. WHERE helps refine search results by specifying which records to target and excluding the ones you don’t want to change. By combining WHERE with UPDATE, you can make updates to any number of records you’d like, from just one to a million or more, with a single UPDATE statement.

As we’ve seen, it’s possible to use UPDATE to update all the records in the table. However, most UPDATE statements add a WHERE clause that filters results, like this:

UPDATE customers SET first_name= ‘Jack’ WHERE id = 5;

This statement will change only one record. The ID column specifies only one value, 5, which refers to a unique ID entry, so the update will apply only to that customer. Regardless of what the first name value previously was, it will change this customer’s first name to Jack. The ID is the most important thing in this statement because it limits the update only to the matching results. The database table ID is usually the primary key of the table, which is used to uniquely identify records in the table. No other record will have the same ID.

The UPDATE statement also allows you to make corrections such as fixing spelling errors or changing capitalization and punctuation. Here’s an example where the UPDATE statement changes the misspelling “jck” to “Jack.”

UPDATE customers SET first_name= ‘Jack’ WHERE first_name= ‘jck’;

SET clauses are used to update the column values. This set clause changes only the records where the value in the first name column equals “jck.” How this query updates the database depends on the type of database being used. The default implementation of MySQL, SQL Server, and other databases are case insensitive. If this is updating a MySQL or similar database, the change will apply to either “jck” or “Jck.” However, when it implements the change, the name in the “first name” column will change to the capitalized version “Jack,” as we typed in the statement.

In the following statement, an improperly capitalized value will also update to the correctly capitalized version of the name “Jack.”

UPDATE customers SET first_name= ‘Jack’ WHERE first_name= ‘jack’;

This statement will change the lower case version “jack” to the correctly capitalized one, “Jack.”  The update changes all the relevant values to the version typed in the statement, regardless of the errors they originally contained.

While these queries will work for case insensitive databases, some databases will be case sensitive, and you will need to use UPPER or LOWER on the column to be sure of the case before comparison. Here is a query that will update all instances of “jck” in the first_name column;

UPDATE customers SET first_name = ‘Jack’ WHERE LOWER(first_name) = ‘jck’;

Updating Multiple Columns at Once

The examples above showed how the UPDATE statement works with a single column. As we’ve noted, you can also update multiple columns at once with a single UPDATE statement combined with the appropriate clauses and operators for refining the query.

In this example, the SQL UPDATE statement updates both the first and last name columns for a single customer:  

UPDATE customers SET first_name= ‘Jack’, last_name = ‘Smith’ WHERE id = 5;

Just as in the previous UPDATE statements, the WHERE clause filters out all other customers in the table except the one with the ID of 5. Then, as the statement specifies, the first and last name columns can update to Jack and Smith, respectively.

Using Multiple WHERE Clause Filters

In the statement above, the WHERE clause allows you to update just one customer record with the ID of 5. However, you may not know the customer’s ID, and you may have just a few basic rules of business logic to help you out. For example, suppose you only know that you have a list of new customers that signed up for a service within the last week, but you need to update the country ID from another table to make the record consistent. You will notice that two records in the example database above are missing country IDs. In situations like this, you can use multiple WHERE clause filters to specify which records you want to update.

First, you’ll need a SELECT query, which retrieves the customer list you need to edit from the first table. Then, you’ll need to retrieve the right country ID from the country table. Here’s an example of a SELECT subquery with the WHERE clause that retrieves the country code for the 33071 zip code.

SELECT id FROM countries WHERE zip_code = ‘33071’;

Next, create a SELECT query that samples the list of records to update. This SELECT statement ensures that the UPDATE statement affects only the records you need. Continuing with this scenario, the SELECT statement below returns a list of all records containing only those users who signed up within the last week.

SELECT * FROM customers WHERE signup >= getDate() – 7 AND zip_code = ‘33071’;

This statement lets you sample the relevant data using the key WHERE clause. The query then returns a list of users who signed up within the previous seven days and also have a zip code that matches the SELECT subquery above. However, this clause only ensures that you’ve returned the correct records to update. You’ll still need to convert this SELECT statement into an UPDATE statement. Here is the UPDATE statement with a placeholder for the subquery we will use to get the country ID:

UPDATE customers SET country_id = <subquery> WHERE signup >= getDate() – 7 and zip_code = ‘33071’;

The UPDATE statement is nearly complete, but we need to replace the subquery placeholder. This is improper SQL syntax. At this point, the query will return an error. Here’s how to combine these two elements to put the complete UPDATE query together:

UPDATE customers SET country_id = (SELECT TOP 1 id FROM countries WHERE zip_code = ‘33071’) WHERE signup >= getDate() – 7 and zip_code = ‘33071’

If you aren’t used to SQL syntax, this query might be confusing. Here’s how it works out. The subquery fetches a country code that matches the 33071 zip code in an external country table. Here, the country is the U.S., so the query retrieves whatever ID is assigned to the U.S. The phrase “top 1” isn’t always necessary, but it acts as a preventative if there’s more than one record for the same zip code in SQL Server.

If the subquery retrieves more than one record, SQL returns an error. In that case, the “top 1” phrase makes sure that the subquery returns only one record. Combined with the WHERE clause that specifies the signup dates within the last week (“getdate” and “-7”), this query should filter results correctly to return the records that need updating.

If the subquery returns more than one record, there’s probably a data integrity problem. For example, if the subquery returns the country code twice, it’s likely that there are duplicate records in the database. That can cause problems later with other SQL statements.

More advanced UPDATE queries

With the query example above, you’d need to run a separate update for each zip code in the database. If you only have a few zip codes to update, that statement is probably enough to handle the task. However, if you have dozens or more, updating each entry individually isn’t very efficient. To solve this problem, you can take this query a step further and create a dynamic SQL statement that allows you to update multiple records at once.

In this case, you’ll need to run the same SELECT query to make sure that the records to update are precisely filtered. This time, you’ll take out the zip code in the WHERE clause in order to update all the records. This string samples the data you need:

SELECT * from customers WHERE signup >= getDate() – 7;

And this is the complete UPDATE query:

UPDATE customers SET country_id = (SELECT TOP 1 id FROM countries WHERE countries.zipcode = customers.zipcode) WHERE signup >= getDate() – 7;

This query updates all the records that have a signup date within the last seven days. The “getDate () – 7” in the WHERE clause filters out all records except those related to signups over the past seven days, and the country zip code clause now includes all user zip codes. It’s important to understand that in this statement, the subquery works dynamically.

The WHERE clause in the subquery includes the customers and the countries tables, linking the two tables to return the country code for each record in the UPDATE statement.

This statement essentially says, “look up the customer’s zip code in the customers’ table and then match that record with the corresponding zip code in the countries table.” When correctly executed, the result should be just one record, but again, “top 1” is added to avoid the error of multiple returns. The matching country code then goes into the users’ table in the country_id column. That allows you to update several zip codes at once and run a dynamic query that will match the user’s address with the appropriate country.

Using the IN statement with UPDATE queries

The WHERE clause is the most important part of an update statement because it determines the location of the records that you want to update in the table. Using an IN clause along with WHERE can help you filter out records even more precisely. 

There are a number of reasons for using an IN clause as part of your update query. For instance, you might want to update only a list of customers who have placed an order, so you would need to get a list of orders from the orders table. This example uses the update statement above, but it changes the business logic, the “real world” business rules that guide how to manage data, for which records the statement should update. In this situation, the statement looks like this:

UPDATE customers SET country_id = (SELECT TOP 1 id FROM countries WHERE countries.zipcode = customers.zipcode) WHERE customer.id IN (SELECT customer_id FROM orders);

This statement updates the same column as above, but it changes only customers from that column who have an order in the orders table. It still uses the same subquery, which ensures that the records with the correct country code from the countries table will update. The only part of the statement that changes is the business logic in the WHERE clause.

This query now updates all customers with an order. However, that can include several records, and it can harm performance if you have other services using the database at the same time. For example, you could limit the search to the number of customers who placed an order within the previous seven days. Here’s how that query will look:

UPDATE customers SET country_id = (SELECT TOP 1 id FROM countries WHERE countries.zipcode = customers.zipcode) WHERE customer.id IN (SELECT customer_id FROM orders WHERE order_date >= getdate() – 7);

In this query, the business logic changes just a little, but it only takes one small change to have a significant effect on the records that update.  Here, instead of retrieving signup dates in the customers table, SQL gets records from the orders table if a customer placed an order within the past seven days. Then the outer update query runs.

This query updates the customers’ table from the list of users returned by the IN subquery statement. It’s also possible to add customers’ signup dates to compile a list of customers who signed up within the last seven days. From those customers, you could only update the ones who placed an order within that time frame. That query would look like this:

UPDATE customers SET country_id = (SELECT TOP 1 id FROM countries WHERE countries.zipcode = customers.zipcode) WHERE signup_date >= getDate() – 7 AND customer.id IN (SELECT customer_id FROM orders WHERE order_date >= getdate() – 7);

Now, the query combines both signup date and order date with an IN clause that includes both SELECT and WHERE to restrict the return set to customers who placed an order within the past seven days.

UPDATE is one of the most common SQL commands, and it’s an essential tool for keeping SQL-powered relational databases current and working properly. Learning its structure and basic commands is a must for programmers, developers, and anyone interested in database management. To learn more about SQL, see our overview of the most useful SQL commands. To learn SQL commands specific to MySQL, check out our MySQL command cheat sheet, or to learn another valuable SQL command in depth: SQL DELETE.