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:
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:
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:
- 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:
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.