Overview
In this tutorial, we will learn about MySQL UPDATE statement. The UPDATE statement modifies the contents of the existing records in the table.
Syntax
The UPDATE statement uses the following syntax:
mysql> UPDATE table_name SET column=expression [, column=expression, …]
[WHERE condition] [other_clauses]
We provide the table name to update, a SET clause that lists one or more column value assignments,
and optionally specify a WHERE clause that identifies which records to update.
Example
Let’s update the Country table in the world MySQL database by changing the Population column
For example, the population of the Argentina increased 20% ad we need to update the Country
table to reflect the new Population.
Before Update
To know the current population of the Argentina country from the Country table:
mysql> SELECT * FROM COUNTRY WHERE Name=’Argentina’ \G
*************************** 1. row ***************************
Code: ARG
Name: Argentina
Continent: South America
Region: South America
SurfaceArea: 2780400.00
IndepYear: 1816
Population: 37032000
LifeExpectancy: 75.1
GNP: 340238.00
GNPOld: 323310.00
LocalName: Argentina
GovernmentForm: Federal Republic
HeadOfState: Fernando de la Rúa
Capital: 69
Code2: AR
1 row in set (0.00 sec)
UPDATE statement:
The column Code is the PRIMARY KEY for the Country table. The column uniquely identifies the rows in the table. The code for Argentina is ARG. Let’s update the Population column for the country using the following update statement:
mysql> UPDATE Country
-> SET Population=Population * 1.2
-> WHERE Code=’ARG’;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
After Update:
mysql> SELECT * FROM COUNTRY WHERE Name=’Argentina’ \G
*************************** 1. row ***************************
Code: ARG
Name: Argentina
Continent: South America
Region: South America
SurfaceArea: 2780400.00
IndepYear: 1816
Population: 44438400
LifeExpectancy: 75.1
GNP: 340238.00
GNPOld: 323310.00
LocalName: Argentina
GovernmentForm: Federal Republic
HeadOfState: Fernando de la Rúa
Capital: 69
Code2: AR
1 row in set (0.00 sec)
Notice the change in the Population column values. There is a 20% increase in the Argentina population as specified in the update statement.
The effects of column assignments made by an UPDATE statement are subject to column type constraints, just like an INSERT or REPLACE statements.
By default, if we attempt to update a column to a value that does not match the column definition, MySQL converts or truncates the value. If we enable strict SQL mode, the server will be more restrictive about allowing invalid values.
It is also possible for an UPDATE statement to have no effect at all. This can occur when it matches no rows for updating. For example, if no rows match the WHERE clause.
UPDATE……ORDER BY
The UPDATE statement by default makes no guarantee about the order in which rows are updated. To avoid problems, we can add an ORDER BY clause to cause the row updates to occur in a particular order.
UPDATE <table_name> SET column=expression
[WHERE condition/s]
ORDER BY <column_name]
UPDATE….LIMIT
The UPDATE allows a LIMIT clause, which places a limit on the number of rows updated. The LIMIT clause should be followed by the row count. For example, to update only one row in the table we can use the LIMIT 1.
The ORDER BY and LIMIT may be used together in the same UPDATE statement
—
MySQL Tutorials
MySQL Tutorials on this website:
https://www.testingdocs.com/mysql-tutorials-for-beginners/
For more information on MySQL Database: