SQL UPDATE Statement: Editing Data in Your Database
Your database may be your organization’s greatest business asset. It holds all your customer data and other key information for working with other entities, such as contractors and vendors. But without proper management, your database could be vulnerable to errors and loss of data.
SQL (Structured Query Language) is an industry-standard programming language that provides the essential tools you need to search your organizational databases and return the necessary results. SQL operates on a straightforward syntax of basic commands (often called statements or queries) combined with a variety of additional clauses and operators. SQL makes it possible to make any needed modifications to your company database and keep it current with the UPDATE statement.
An SQL UPDATE statement will modify existing records in your database tables 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 overwrite records accidentally 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.
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.
At the very minimum, an SQL UPDATE statement looks something like this:
UPDATE customer set first name= ‘Jack’
Here, the UPDATE statement sets all selected records to the first name “Jack.” The statement first identifies the table you want to change: “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.
But although this is a valid SQL statement, it’s not a typical UPDATE query, and it’s most likely not what you want to do when you’re making changes to essential database values. As it appears here, this statement changes all records in the specified column “customer set” to “Jack.” That kind of global change can be disastrous, forcing you to recover previous versions with a backup.
The SQL WHERE clause can prevent this. 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 like — from just one to a million or more– with a single UPDATE statement.
Adding the WHERE Clause to an UPDATE Statement
As we’ve seen, it’s possible to use UPDATE to update all the records in the table. But most UPDATE statements add a WHERE clause that filters results, like this:
update customer set first_name= ’Jack’ where customerId = 5
Now, the statement changes only one record. The “customerId” column specifies only one value, “5,” which refers to a unique customer ID entry, so the update will apply only to that customer. And regardless of what the first name value previously was, it will change this customer’s first name to “Jack.” The most important thing in this statement is the customer ID because it limits the update only to the matching results.
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 customer set first_name= ’Jack’ where first_name= ‘jck’
This statement changes only the records where the first name (in the “first name” column) equals “jck.” Here, capitalization doesn’t matter, so 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 customer 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.
Updating Multiple Columns at Once
The examples above showed how the UPDATE statement works with just one column. But 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 customer set first_name= ’Jack’, last_name = ’Smith’ where customerId = 5
Just as in the previous UPDATE statements, the WHERE clause filters out all other customers in the table except the one with the “customerId” 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 “customerID” of 5. But 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. In situations like this, you can use multiple WHERE clause filters to specify which records you want to update.
First, you’ll need the 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 other country table. Here’s an example of a SELECT subquery with the WHERE clause that retrieves the country code for the zipcode “33071.”
select countryid from countries where zipcode = ‘33071’
Next, you’ll 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 users where signup_date >= 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. But 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, which looks like this:
update users set countryid = <subquery> where signup_date >= getDate() – 7 and zip_code = ‘33071’
Now, the UPDATE statement is nearly complete. Note that this statement contains a “<subquery>” placeholder. This is improper SQL syntax, so at this point, the query will return an error. Here’s how all the elements combine to put the complete UPDATE query together:
update users set countryid = (select top 1 countryid from countries where zipcode = ‘33071’) where signup_date >= getDate() – 7 and zip_code = ‘33071’
If you aren’t used to SQL syntax, this query might be confusing. But here’s how it works out. The subquery fetches a country code that matches the zipcode “33071” from an external country table. Here, the country is the US, so the query retrieves whatever ID is assigned to the US. 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.
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 desired results.
But 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 as stated 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. But 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. But 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 users where signup_date >= getDate() – 7
And this is the complete UPDATE query:
update users set countryid = (select top 1 countryid from countries where countries.zipcode = users.zipcode) where signup_date >= 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 actually executes the update.
The WHERE clause in the subquery includes the users 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 user’s zip code in the users’ 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 “countryID” 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 most important part of an update statement is the WHERE clause because it determines the location of the records that you want to update in the table. Using an IN clause along with WHERE can 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 users set countryid = (select top 1 countryid from countries where countries.zipcode = users.zipcode) where customerId in (select customerId 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. But that can include several records, and it can harm performance if you have other services running off 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 users set countryid = (select top 1 countryid from countries where countries.zipcode = users.zipcode) where customerId in (select customerId from orders where order_date >= getdate() - 7)
In this query, the business logic changes just a little — granted, 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 user placed an order within the past seven days. Then the “outer” update query runs.
This query updates the users’ table from the list of users returned by the IN subquery statement. It’s also possible to add users’ signup dates to compile a list of users who signed up within the last seven days. From those users, you could only update the ones who placed an order within that time frame. That query would look like this:
update users set countryid = (select top 1 countryid from countries where countries.zipcode = users.zipcode) where signup_date >= getDate() – 7 and customerId in (select customerId 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. Or get an in-depth look at another staple SQL statement — SQL DELETE.
Top courses in SQL
SQL students also learn
Empower your team. Lead the industry.
Get a subscription to a library of online courses and digital learning tools for your organization with Udemy for Business.