UPDATE
query is used to update existing records.
Syntax
UPDATE
table_name
SET
col1=val1, col2=val2….colN=valN
[WHERE
condition];
mysql> SELECT * FROM employee; +----+---------+------+-------------+---------------------+------------+ | id | name | age | salary | mailId | city | +----+---------+------+-------------+---------------------+------------+ | 1 | Hari | 28 | 12345.6700 | hari@hari.com | Bangalore | | 2 | Sandesh | 31 | 98345.0000 | sandesh@sandesh.com | Trivendram | | 3 | Phalgum | 33 | 119345.6700 | phalgun@hari.com | Hyderabad | | 4 | Manju | 36 | 87666.8700 | manju@sandesh.com | Bangalore | | 5 | Rakesh | 26 | 38000.0000 | rakesh@hari.com | Bangalore | | 6 | Sankalp | 38 | 87645.6700 | sankalp@sankalp.com | Chenai | | 7 | Vadiraj | 40 | 12345.6700 | vadi@hari.com | Bangalore | | 8 | Prasob | 37 | 12345.6700 | prasob@sandesh.com | Trivendram | | 9 | Kesav | NULL | 123457.8900 | NULL | NULL | +----+---------+------+-------------+---------------------+------------+ 9 rows in set (0.00 sec)
a. Change the age of an employee
with id 1 to 23.
mysql> UPDATE employee SET age=23 WHERE id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM employee; +----+---------+------+-------------+---------------------+------------+ | id | name | age | salary | mailId | city | +----+---------+------+-------------+---------------------+------------+ | 1 | Hari | 23 | 12345.6700 | hari@hari.com | Bangalore | | 2 | Sandesh | 31 | 98345.0000 | sandesh@sandesh.com | Trivendram | | 3 | Phalgum | 33 | 119345.6700 | phalgun@hari.com | Hyderabad | | 4 | Manju | 36 | 87666.8700 | manju@sandesh.com | Bangalore | | 5 | Rakesh | 26 | 38000.0000 | rakesh@hari.com | Bangalore | | 6 | Sankalp | 38 | 87645.6700 | sankalp@sankalp.com | Chenai | | 7 | Vadiraj | 40 | 12345.6700 | vadi@hari.com | Bangalore | | 8 | Prasob | 37 | 12345.6700 | prasob@sandesh.com | Trivendram | | 9 | Kesav | NULL | 123457.8900 | NULL | NULL | +----+---------+------+-------------+---------------------+------------+ 9 rows in set (0.00 sec)
b. Increment salaries of employees
by 10%.
mysql> UPDATE employee SET salary=salary*1.1; Query OK, 9 rows affected (0.00 sec) Rows matched: 9 Changed: 9 Warnings: 0
mysql> SELECT * FROM employee; +----+---------+------+-------------+---------------------+------------+ | id | name | age | salary | mailId | city | +----+---------+------+-------------+---------------------+------------+ | 1 | Hari | 23 | 13580.2370 | hari@hari.com | Bangalore | | 2 | Sandesh | 31 | 108179.5000 | sandesh@sandesh.com | Trivendram | | 3 | Phalgum | 33 | 131280.2370 | phalgun@hari.com | Hyderabad | | 4 | Manju | 36 | 96433.5570 | manju@sandesh.com | Bangalore | | 5 | Rakesh | 26 | 41800.0000 | rakesh@hari.com | Bangalore | | 6 | Sankalp | 38 | 96410.2370 | sankalp@sankalp.com | Chenai | | 7 | Vadiraj | 40 | 13580.2370 | vadi@hari.com | Bangalore | | 8 | Prasob | 37 | 13580.2370 | prasob@sandesh.com | Trivendram | | 9 | Kesav | NULL | 135803.6790 | NULL | NULL | +----+---------+------+-------------+---------------------+------------+ 9 rows in set (0.00 sec)
If you
don’t specify any condition, then it reflects entire table.
No comments:
Post a Comment