SQL UPDATE : Learn How To Update Values In A Table

While using databases, we may often want to update a few data values either in a single record or for multiple records. Structured Query Language(SQL) provides various commands to access, retrieve and manage databases. Out of the lot, one such command is the UPDATE command. The UPDATE command is used to update data existing in a table. The following topics will be covered in this article:

1. UPDATE statement
2. Syntax
3. Examples:
   • Update single record 
   • Use the statement for multiple records
   • Update data by omitting WHERE Clause
   • Use the statement to update data from another table

SQL UPDATE statement

The UPDATE command is used to modify a single record or multiple records existing in a table. 

Syntax:

UPDATE TableName
SET Column1 = Value1, Column2 = Value2, …, ColumnN = ValueN
WHERE Condition;

Here, the WHERE clause specifies which records must be updated. Just in case, you omit the WHERE clause, all the records existing in the table will be updated. 

Since you have understood the syntax, let us now discuss the various ways to use it with examples.

Examples:

For your better understanding, I have divided the examples into the following sections:

• Update single record 
• Use the statement for multiple records
• Update data by omitting WHERE Clause
• Use the statement to update data from another table

I am going to consider the following table to explain to you the examples:

Let us take a look at each one of them. 

Update Single Record

Example: 

Write a query to update the 3rd employee (Employee ID) with a new phone number and city.

UPDATE Employees
SET PhoneNumber ='9646879876', City= 'Kolkata'
WHERE EmpID = 3;

Output:

You will see the following table as output:

Next, in this article, let us understand how to update data values in multiple records.

Update Multiple Records

To update multiple records in the table, we must use the WHERE clause. The WHERE clause determines the number of records that will be updated.

Example: 

Write a query to update the employees EmpEmail to sample@abc.com  for all records to city name Delhi.

UPDATE Employees
Set EmpEmail = 'sample@abc.com’
WHERE City =‘Delhi’;

Output:

You will see the following table as output:

Moving on in this article, let us understand how to update the data of a table by omitting the WHERE clause.

Update data by omitting WHERE Clause

When we omit the WHERE clause while using the UPDATE statement in SQL, then there is no limit set on the number of records that must be updated. So, all the records will be updated automatically.

Example: 

Write a query to update the employees’ emails to example@xyz.com.

UPDATE Employees
Set EmpEmail = 'example@xyz.com’;

Output:

You will see the following table as output:

Master the language of databases with our comprehensive SQL Course.

Find out our MS SQL Course in Top Cities

Next in this article, let us understand how to update data of a specific table from another table.

Update data from another table

We can use the UPDATE statement to update the data of a specific table considering the data of another table. 

Let us consider the following table:

Example: 

Write a query to update the employees’ names by taking the data from the contacts table.

UPDATE Employees
SET EmpName = (SELECT EmpName
                  FROM Contacts
                  WHERE Contacts.City = Employees.City);

Output:

You will see the following table as output:

 We can also rewrite the above query as follows:

UPDATE Employees
SET Employees.EmpName = Contacts.EmpName
FROM Employees
INNER JOIN Contacts
ON (Employees.City = Contacts.City);

So, folks that is how you can use the UPDATE statement in SQL. With that, we come to an end to this article on SQL UPDATE. I hope you found this article informative.

If you wish to learn more about MySQL and get to know this open-source relational database, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in-depth and help you achieve mastery over the subject.

Got a question for us? Please mention it in the comments section of this article on “SQL UPDATE” and I will get back to you.