SQL UPDATE Query
The UPDATE Query is used to modify the existing records in a table. if you are working with update query you should know that when updating records in a table. Notice the WHERE clause in the UPDATE Query. The WHERE clause specifies which records that should be updated. otherwise all the rows would be affected.
The basic syntax of the UPDATE query with a WHERE clause is as follows −
UPDATE table_name SET column_name1 = value1, column_name2 = value2...., columnN = valueN WHERE [define your condition];Consider the tbl_student_record table having the following records.
| id | name | class | subject | city | phone | fee | fee_date |
|---|---|---|---|---|---|---|---|
| 1 | Mohan | 2nd | Math | Agra | 999999990 | 500 | 2018-01-02 |
| 2 | Pooja | 3rd | English | Delhi | 999999991 | 200 | 2018-01-11 |
| 3 | Rahul | 4th | Math | Agra | 999999992 | 300 | 2018-01-05 |
| 4 | Suresh | 2nd | English | Agra | 999999993 | 400 | 2018-01-22 |
| 5 | Vivek | 3rd | Math | Agra | 999999994 | 500 | 2018-01-01 |
| 6 | Anuj | 2nd | English | Delhi | 999999995 | 600 | 2018-01-31 |
| 7 | Sanju | 4th | Math | Agra | 999999996 | 400 | 2018-01-17 |
| 8 | Rohit | 3rd | English | Delhi | 999999997 | 700 | 2018-01-10 |
| 9 | Mahesh | 3rd | Math | Agra | 999999998 | 800 | 2018-01-24 |
| 10 | Munesh | 4th | English | Delhi | 999999999 | 200 | 2018-01-09 |
Example 1 : The following query will update the subject for a student whose ID number is 3 in the table.
UPDATE tbl_student_record SET subject = 'Art' WHERE id =3;After Query Execution : Record No 3 Subject has been updated.
| id | name | class | subject | city | phone | fee | fee_date |
|---|---|---|---|---|---|---|---|
| 1 | Mohan | 2nd | Math | Agra | 999999990 | 500 | 2018-01-02 |
| 2 | Pooja | 3rd | English | Delhi | 999999991 | 200 | 2018-01-11 |
| 3 | Rahul | 4th | Art | Agra | 999999992 | 300 | 2018-01-05 |
| 4 | Suresh | 2nd | English | Agra | 999999993 | 400 | 2018-01-22 |
| 5 | Vivek | 3rd | Math | Agra | 999999994 | 500 | 2018-01-01 |
| 6 | Anuj | 2nd | English | Delhi | 999999995 | 600 | 2018-01-31 |
| 7 | Sanju | 4th | Math | Agra | 999999996 | 400 | 2018-01-17 |
| 8 | Rohit | 3rd | English | Delhi | 999999997 | 700 | 2018-01-10 |
| 9 | Mahesh | 3rd | Math | Agra | 999999998 | 800 | 2018-01-24 |
| 10 | Munesh | 4th | English | Delhi | 999999999 | 200 | 2018-01-09 |
Example 2 : The following query will update the subject for a student whose City Agra in the table.
UPDATE tbl_student_record SET subject = 'Art' WHERE city ='Agra';After Query Execution : Subject has been updated whose City agra in the table.
| id | name | class | subject | city | phone | fee | fee_date |
|---|---|---|---|---|---|---|---|
| 1 | Mohan | 2nd | Art | Agra | 999999990 | 500 | 2018-01-02 |
| 2 | Pooja | 3rd | English | Delhi | 999999991 | 200 | 2018-01-11 |
| 3 | Rahul | 4th | Art | Agra | 999999992 | 300 | 2018-01-05 |
| 4 | Suresh | 2nd | Art | Agra | 999999993 | 400 | 2018-01-22 |
| 5 | Vivek | 3rd | Art | Agra | 999999994 | 500 | 2018-01-01 |
| 6 | Anuj | 2nd | English | Delhi | 999999995 | 600 | 2018-01-31 |
| 7 | Sanju | 4th | Art | Agra | 999999996 | 400 | 2018-01-17 |
| 8 | Rohit | 3rd | English | Delhi | 999999997 | 700 | 2018-01-10 |
| 9 | Mahesh | 3rd | Art | Agra | 999999998 | 800 | 2018-01-24 |
| 10 | Munesh | 4th | English | Delhi | 999999999 | 200 | 2018-01-09 |