yeeit
Waifu Economist
2
MONTHS
2 2 MONTHS OF SERVICE
LEVEL 1
400 XP
When you insert record to your table there are some cases that you need to modify the data. Or you may want to update it to the latest record.
The SQL update is there to help us modify the existing values of the table, the SQL update statement used the same column name as insert statement and the new value that you want to placed and the table name to update the data successfully.
The syntax of SQL update statement is as follows:
Consider the same table we had in previous tutorials.
Users:
Firstname
Lastname
Age
Maritalstatus
Country
John
Smith
40
Married
Usa
Mathew
Simon
30
Married
UK
Bill
Steve
20
Single
Usa
Amanda
Rogers
28
Married
Germany
Steve
Hills
30
Single
france
Tom
Jerry
20
Single
USA
Bill
Josh
30
Married
UK
Let’s say that john smith has migrated from USA to Australia then we want to update his country, then we write the SQL statement as:
UPDATE
Users SET
Country=
”Australia” WHERE
(
Firstname=
”John” AND
Lastname=
”Smith”)
The table will be updated and will change to:
Firstname
Lastname
Age
Maritalstatus
Country
John
Smith
40
Married
Australia
Mathew
Simon
30
Married
UK
Bill
Steve
20
Single
Usa
Amanda
Rogers
28
Married
Germany
Steve
Hills
30
Single
france
Tom
Jerry
20
Single
USA
Bill
Josh
30
Married
UK
Now consider multiple update, we want to change the age and marital status of Bill in the table.
NOTE: We have 2 persons named as Bill both will be updated.
UPDATE
Users SET
age=
”50
”,
Maritalstatus=
”Married” WHERE
Firstname=
”Bill”
The table is modified as follows:
Firstname
Lastname
Age
Maritalstatus
Country
John
Smith
40
Married
Australia
Mathew
Simon
30
Married
UK
Bill
Steve
50
Married
Usa
Amanda
Rogers
28
Married
Germany
Steve
Hills
30
Single
france
Tom
Jerry
20
Single
USA
Bill
Josh
50
Married
UK
So it is always a good idea to select rows with primary keys, or unique IDs to avoid unwanted data changes.
If we accidently or somehow forgot to type the where clause in the update query then all the data will be updated with the value you provide.
For example:
UPDATE
Users SET
Age=
”10
”
The result would be:
Firstname
Lastname
Age
Maritalstatus
Country
John
Smith
10
Married
Australia
Mathew
Simon
10
Married
UK
Bill
Steve
10
Married
Usa
Amanda
Rogers
10
Married
Germany
Steve
Hills
10
Single
france
Tom
Jerry
10
Single
USA
Bill
Josh
10
Married
UK
So make sure that you always use the where clause to avoid unnecessary lose of data.
The SQL update is there to help us modify the existing values of the table, the SQL update statement used the same column name as insert statement and the new value that you want to placed and the table name to update the data successfully.
The syntax of SQL update statement is as follows:
- UPDATE
TABLE_NAME
- SET
column1=
value1,
column2=
value2,...
- WHERE
COLUMN
=
VALUE
Consider the same table we had in previous tutorials.
Users:
Firstname
Lastname
Age
Maritalstatus
Country
John
Smith
40
Married
Usa
Mathew
Simon
30
Married
UK
Bill
Steve
20
Single
Usa
Amanda
Rogers
28
Married
Germany
Steve
Hills
30
Single
france
Tom
Jerry
20
Single
USA
Bill
Josh
30
Married
UK
Let’s say that john smith has migrated from USA to Australia then we want to update his country, then we write the SQL statement as:
UPDATE
Users SET
Country=
”Australia” WHERE
(
Firstname=
”John” AND
Lastname=
”Smith”)
The table will be updated and will change to:
Firstname
Lastname
Age
Maritalstatus
Country
John
Smith
40
Married
Australia
Mathew
Simon
30
Married
UK
Bill
Steve
20
Single
Usa
Amanda
Rogers
28
Married
Germany
Steve
Hills
30
Single
france
Tom
Jerry
20
Single
USA
Bill
Josh
30
Married
UK
Now consider multiple update, we want to change the age and marital status of Bill in the table.
NOTE: We have 2 persons named as Bill both will be updated.
UPDATE
Users SET
age=
”50
”,
Maritalstatus=
”Married” WHERE
Firstname=
”Bill”
The table is modified as follows:
Firstname
Lastname
Age
Maritalstatus
Country
John
Smith
40
Married
Australia
Mathew
Simon
30
Married
UK
Bill
Steve
50
Married
Usa
Amanda
Rogers
28
Married
Germany
Steve
Hills
30
Single
france
Tom
Jerry
20
Single
USA
Bill
Josh
50
Married
UK
So it is always a good idea to select rows with primary keys, or unique IDs to avoid unwanted data changes.
If we accidently or somehow forgot to type the where clause in the update query then all the data will be updated with the value you provide.
For example:
UPDATE
Users SET
Age=
”10
”
The result would be:
Firstname
Lastname
Age
Maritalstatus
Country
John
Smith
10
Married
Australia
Mathew
Simon
10
Married
UK
Bill
Steve
10
Married
Usa
Amanda
Rogers
10
Married
Germany
Steve
Hills
10
Single
france
Tom
Jerry
10
Single
USA
Bill
Josh
10
Married
UK
So make sure that you always use the where clause to avoid unnecessary lose of data.