SQL UPDATE

The purpose of the update statement is to change the value of one or more fields in a table.

The following update statement’s purpose is to change the phone number of a client called Aamir Rao:

update customers
set
phone_number=4934455599988
where first_name = 'Aamir'
and last_name = 'Rao'

In this statement:

  • customers is the name of the table.
  • phone_number is the column for which the value will change.
  • 4934455599988 is the value that the phone_number should take.
  • where first_name = ‘Aamir’ and last_name = ‘Rao’ is the condition that rows in the table customers must fulfill to be modified.

This diagram shows how the update is functioning:

Does the syntax change when you change the value of a field containing text and not numbers?

When the value that the column should take contains text, then you put this value inside quotes:

update customers
set email='[email protected]'
where first_name = 'Aamir'
and last_name = 'Rao'

Is it possible to update two columns at a time?

It is possible to update several columns at a time. As many as you want. When executing two values at a time, the update looks like this:

update customers
set email='[email protected]'
,phone_number=34455599988
where customer_id = 9987

Notice that:

  • we separated the two value assignments by a comma (“,”).
  • we used the word “set” only once.

How to multiply values of a column by 2?

It is not hard at all. Here is an example:

update salespersons
set revenue = revenue * 2
where region = 'Karnataka'

This update statement multiplies the value of the column “revenue” by 2. It does this for all rows for which the region column has the value “Karnatka”.
Here “*” means “multiply by”.

You can also use other mathematical signs like “+”,”-”,”/” if you want to add a value, subtract a value or divide by a value.

Is it compulsory to write a condition (“where …”) in an update statement ?

It is not compulsory, but be aware that if you do not put any condition, then you will update all rows in the table.
 

If you want to test your understanding of this lesson, take the SQL Update Quiz. You can also go to the next lesson about SQL Insert.
 
Share on:

Something not clear? Ask your question here:

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

 
 

Author:
Last udpated: September 10, 2017
Created: May 17, 2014