The SQL UPDATE Statement
The UPDATE statement is used to modify the existing records in a table.
UPDATE Syntax
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
Be careful when updating records in a table!
------------
Be careful when updating records. If you omit the WHERE clause, ALL records will be updated!
Setting environment for using XAMPP for Windows.
Mounika@MOUNIKA-PC c:\xampp
# mysql -h localhost -u root
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.4.18-MariaDB mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> USE vlrinst;
Database changed
MariaDB [vlrinst]> select 8 from emp
-> ;
+---+
| 8 |
+---+
| 8 |
| 8 |
| 8 |
| 8 |
| 8 |
| 8 |
| 8 |
| 8 |
| 8 |
| 8 |
| 8 |
| 8 |
| 8 |
+---+
13 rows in set (0.101 sec)
MariaDB [vlrinst]> update emp set sal = sal+(sal*.3) ;
Query OK, 13 rows affected (0.148 sec)
Rows matched: 13 Changed: 13 Warnings: 0
MariaDB [vlrinst]> select * from emp;
+----+---------+-----------+------+-------+
| id | name | loc | age | sal |
+----+---------+-----------+------+-------+
| 1 | venkat | kphb | 35 | 39000 |
| 2 | praveen | pune | 28 | 45500 |
| 3 | mounika | hyderabad | 22 | 50700 |
| 4 | revathi | jntu | 26 | 26000 |
| 5 | anji | gutta | 28 | 26000 |
| 6 | harika | lb nagar | 26 | 39130 |
| 7 | praveen | jntu | 33 | 130 |
| 8 | pandu | munipeda | 35 | 39390 |
| 9 | pandu | pune | 23 | 51090 |
| 10 | venkat | kphb | 35 | 39000 |
| 11 | praveen | pune | 28 | 45500 |
| 12 | mounika | hyderabad | 22 | 50700 |
| 13 | revathi | jntu | 26 | 26000 |
+----+---------+-----------+------+-------+
13 rows in set (0.001 sec)
MariaDB [vlrinst]> update emp set sal = sal+(sal*.3) ;
Query OK, 13 rows affected (0.091 sec)
Rows matched: 13 Changed: 13 Warnings: 0
MariaDB [vlrinst]> select * from emp;
+----+---------+-----------+------+-------+
| id | name | loc | age | sal |
+----+---------+-----------+------+-------+
| 1 | venkat | kphb | 35 | 50700 |
| 2 | praveen | pune | 28 | 59150 |
| 3 | mounika | hyderabad | 22 | 65910 |
| 4 | revathi | jntu | 26 | 33800 |
| 5 | anji | gutta | 28 | 33800 |
| 6 | harika | lb nagar | 26 | 50869 |
| 7 | praveen | jntu | 33 | 169 |
| 8 | pandu | munipeda | 35 | 51207 |
| 9 | pandu | pune | 23 | 66417 |
| 10 | venkat | kphb | 35 | 50700 |
| 11 | praveen | pune | 28 | 59150 |
| 12 | mounika | hyderabad | 22 | 65910 |
| 13 | revathi | jntu | 26 | 33800 |
+----+---------+-----------+------+-------+
13 rows in set (0.001 sec)
MariaDB [vlrinst]> update emp set name='ramesh' where id=1;
Query OK, 1 row affected (0.124 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [vlrinst]> select * from emp;
+----+---------+-----------+------+-------+
| id | name | loc | age | sal |
+----+---------+-----------+------+-------+
| 1 | ramesh | kphb | 35 | 50700 |
| 2 | praveen | pune | 28 | 59150 |
| 3 | mounika | hyderabad | 22 | 65910 |
| 4 | revathi | jntu | 26 | 33800 |
| 5 | anji | gutta | 28 | 33800 |
| 6 | harika | lb nagar | 26 | 50869 |
| 7 | praveen | jntu | 33 | 169 |
| 8 | pandu | munipeda | 35 | 51207 |
| 9 | pandu | pune | 23 | 66417 |
| 10 | venkat | kphb | 35 | 50700 |
| 11 | praveen | pune | 28 | 59150 |
| 12 | mounika | hyderabad | 22 | 65910 |
| 13 | revathi | jntu | 26 | 33800 |
+----+---------+-----------+------+-------+
13 rows in set (0.001 sec)
MariaDB [vlrinst]> update emp set name='ramesh1',age=33,loc='jntu' where id=1;
Query OK, 1 row affected (0.068 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [vlrinst]> select * from emp;
+----+---------+-----------+------+-------+
| id | name | loc | age | sal |
+----+---------+-----------+------+-------+
| 1 | ramesh1 | jntu | 33 | 50700 |
| 2 | praveen | pune | 28 | 59150 |
| 3 | mounika | hyderabad | 22 | 65910 |
| 4 | revathi | jntu | 26 | 33800 |
| 5 | anji | gutta | 28 | 33800 |
| 6 | harika | lb nagar | 26 | 50869 |
| 7 | praveen | jntu | 33 | 169 |
| 8 | pandu | munipeda | 35 | 51207 |
| 9 | pandu | pune | 23 | 66417 |
| 10 | venkat | kphb | 35 | 50700 |
| 11 | praveen | pune | 28 | 59150 |
| 12 | mounika | hyderabad | 22 | 65910 |
| 13 | revathi | jntu | 26 | 33800 |
+----+---------+-----------+------+-------+
13 rows in set (0.000 sec)
MariaDB [vlrinst]> update emp set age=29 where age=26;
Query OK, 3 rows affected (0.059 sec)
Rows matched: 3 Changed: 3 Warnings: 0
MariaDB [vlrinst]> update emp set sal=sal+(sal*.2) ;
Query OK, 13 rows affected (0.092 sec)
Rows matched: 13 Changed: 13 Warnings: 0
MariaDB [vlrinst]> select * from emp;
+----+---------+-----------+------+-------+
| id | name | loc | age | sal |
+----+---------+-----------+------+-------+
| 1 | ramesh1 | jntu | 33 | 60840 |
| 2 | praveen | pune | 28 | 70980 |
| 3 | mounika | hyderabad | 22 | 79092 |
| 4 | revathi | jntu | 29 | 40560 |
| 5 | anji | gutta | 28 | 40560 |
| 6 | harika | lb nagar | 29 | 61043 |
| 7 | praveen | jntu | 33 | 203 |
| 8 | pandu | munipeda | 35 | 61448 |
| 9 | pandu | pune | 23 | 79700 |
| 10 | venkat | kphb | 35 | 60840 |
| 11 | praveen | pune | 28 | 70980 |
| 12 | mounika | hyderabad | 22 | 79092 |
| 13 | revathi | jntu | 29 | 40560 |
+----+---------+-----------+------+-------+
13 rows in set (0.001 sec)
MariaDB [vlrinst]> update emp set sal=sal+10000 where sal <41000 ;
Query OK, 4 rows affected (0.074 sec)
Rows matched: 4 Changed: 4 Warnings: 0
MariaDB [vlrinst]> select * from emp;
+----+---------+-----------+------+-------+
| id | name | loc | age | sal |
+----+---------+-----------+------+-------+
| 1 | ramesh1 | jntu | 33 | 60840 |
| 2 | praveen | pune | 28 | 70980 |
| 3 | mounika | hyderabad | 22 | 79092 |
| 4 | revathi | jntu | 29 | 50560 |
| 5 | anji | gutta | 28 | 50560 |
| 6 | harika | lb nagar | 29 | 61043 |
| 7 | praveen | jntu | 33 | 10203 |
| 8 | pandu | munipeda | 35 | 61448 |
| 9 | pandu | pune | 23 | 79700 |
| 10 | venkat | kphb | 35 | 60840 |
| 11 | praveen | pune | 28 | 70980 |
| 12 | mounika | hyderabad | 22 | 79092 |
| 13 | revathi | jntu | 29 | 50560 |
+----+---------+-----------+------+-------+
13 rows in set (0.001 sec)
MariaDB [vlrinst]>