SQL UPDATE can update some or all table records

<<Previous - SQL Insert into

Next - SQL Select Table >>

SQL UPDATE statement is used to modify or change the existing record in the table.

SQL UPDATE - Syntax

UPDATE Table-name SET column1 = value1, column 2= value2,..... columnN = valueN
WHERE[condition];

Following SQL query will update the City to ‘Kerala’ for the record of RollNo = 3.

mysql> UPDATE student SET City = 'Kerala' WHERE RollNo = 3;
Query OK, 1 row affected (0.37 sec)
Rows matched: 1  Changed: 1  Warnings: 0

By using the SELECT statement, we can check whether the particular record changed.

mysql> select * from student;
+--------+---------+------+-----------+
| RollNo | Name    | Age  | City      |
+--------+---------+------+-----------+
|      1 | Aruna   |   18 | Chennai   |
|      2 | Varun   |   19 | Bangalore |
|      3 | Ara     |   19 | Kerala    |
|      4 | Markdin |   18 | Mumbai    |
|      5 | Kannan  |   20 | Kerala    |
|      6 | Kanika  |   18 | Chennai   |
+--------+---------+------+-----------+
6 rows in set (0.13 sec)

Example of Neglecting WHERE clause

If you neglect the WHERE clause, the particular column of all the records will be changed.

mysql> UPDATE student SET Age = 21;
Query OK, 6 rows affected (0.15 sec)
Rows matched: 6  Changed: 6  Warnings: 0

mysql> select * from student;
+--------+---------+------+-----------+
| RollNo | Name    |  Age | City      |
+--------+---------+------+-----------+
|      1 | Aruna   |   21 | Chennai   |
|      2 | Varun   |   21 | Bangalore |
|      3 | Ara     |   21 | Kerala    |
|      4 | Markdin |   21 | Mumbai    |
|      5 | Kannan  |   21 | Kerala    |
|      6 | Kanika  |   21 | Chennai   |
+--------+---------+------+-----------+
6 rows in set (0.01 sec)

<< Previous - SQL Insert into

Next - SQL Select Table >>