AWS Global Infrastructure

Databases

Topics Covered
  • Cassandra (14 Blogs)
  • MongoDB Dev and Admin (15 Blogs)
  • MySQL (55 Blogs)
  • SQL Essentials Training and Certification (9 Blogs)
SEE MORE

MI-new-launch

myMock Interview Service for Real Tech Jobs

myMock-widget-banner-bg

SQL UPDATE : Learn How To Update Values In A Table

Published on Oct 07,2019 54 Views
15 / 37 Blog from Introduction to SQL

MI-new-launch

myMock Interview Service for Real Tech Jobs

myMock-mobile-banner-bg

myMock Interview Service for Real Tech Jobs

  • Mock interview in latest tech domains i.e JAVA, AI, DEVOPS,etc
  • Get interviewed by leading tech experts
  • Real time assessment report and video recording

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:

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:

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

EmpIDEmpNameEmpEmailPhoneNumberCity

1

Mohan

mohan@xyz.com

9966449966

Delhi

2

Sonia

sonia@abc.com

9746964799

Mumbai

3

Sanjay

sanjay@pqr.com

9654323456

Bengaluru

4

Avni

avni@xyz.com

9876543678

Mumbai

5

Rahul

rahul@abc.com

9542456786

Delhi

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:

EmpIDEmpNameEmpEmailPhoneNumberCity

1

Mohan

mohan@xyz.com

9966449966

Delhi

2

Sonia

sonia@abc.com

9746964799

Mumbai

3

Sanjay

sanjay@pqr.com

9646879876

Kolkata

4

Avni

avni@xyz.com

9876543678

Mumbai

5

Rahul

rahul@abc.com

9542456786

Delhi

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:

EmpIDEmpNameEmpEmailPhoneNumberCity

1

Mohan

sample@abc.com

9966449966

Delhi

2

Sonia

sonia@abc.com

9746964799

Mumbai

3

Sanjay

sanjay@pqr.com

9646879876

Kolkata

4

Avni

avni@xyz.com

9876543678

Mumbai

5

Rahul

sample@abc.com

9542456786

Delhi

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:

EmpIDEmpNameEmpEmailPhoneNumberCity

1

Mohan

example@xyz.com

9966449966

Delhi

2

Sonia

example@xyz.com

9746964799

Mumbai

3

Sanjay

example@xyz.com

9646879876

Kolkata

4

Avni

example@xyz.com

9876543678

Mumbai

5

Rahul

example@xyz.com

9542456786

Delhi

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:

ContactIDContactNameContactEmailPhoneNumberCity

1

Mohan Sharma

contactmohan@xyz.com

9962449966

Delhi

2

Sonia Khanna

contactsonia@xyz.com

9461964799

Mumbai

3

Sanjay Kapoor

contactsanjay@xyz.com

9719879876

Kolkata

4

Avni Mishra

contactavni@xyz.com

9889743678

Mumbai

5

Rahul Roy

contactrahul@xyz.com

9818256786

Delhi

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:

EmpIDEmpNameEmpEmailPhoneNumberCity

1

Mohan Sharma

example@xyz.com

9966449966

Delhi

2

Sonia Khanna

example@xyz.com

9746964799

Mumbai

3

Sanjay Kapoor

example@xyz.com

9646879876

Kolkata

4

Avni Mishra

example@xyz.com

9876543678

Mumbai

5

Rahul Roy

example@xyz.com

9542456786

Delhi

 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.

Comments
0 Comments

Browse Categories

webinar REGISTER FOR FREE WEBINAR
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP

Subscribe to our Newsletter, and get personalized recommendations.