Update MySQL Tables

Tutorials

Mysql

Topic

How to modify or Update an existing MySQL table's column or row using UPDATE table query?

Explanation

Update MySQL Tables:

The Update query is used to change or modify the existing values in a table.

The Update Syntax is


UPDATE tbl_name SET 
col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_condition];

The Update query updates the columns of existing rows in a table with the new values. The SET clause is used to indicate which columns to be modified. The WHERE clause is used to specify the conditions that identify which rows to be updated.

The following example will set the address of the student to a new address.


mysql> Update student set address='welling street' where
address='victoria street';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

But this will set all the address of the students who ever lives in victoria street will be changed to welling street.

Suppose if we want to set the address of a single student to a new address then we can choose the below option. We can Update MySQL tables using Where query.


mysql> Update student set address='welling street' where name='jack';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

If we want to change a students mark we can use the below statement.


mysql> Update student set marks=100 where name='david';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

This can also be rewritten as the following.


mysql> Update student set marks=marks+2 where name='david';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

In Update query we can also use the arithmetic operations. Using these queries we can modify or Update MySQL tables.