The SQL UPDATE command updates the data in tables. Its format is quite simple.
UPDATE table_name SET
column_name1 = value1,
column_name2 = value2,
column_name3 = value3 …
[WHERE conditions];
Obviously, like other SQL commands, you can type in in one line or multiple lines.
Let's look at some examples.
Bignet has been doing good business, the CEO increases his salary by $20000 and perks by $5000. His previous salary was $200000 and perks were $50000.
UPDATE employee_data SET
salary=220000, perks=55000
WHERE title='CEO';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
You can test this out by listing the data in the table.
select salary, perks from
employee_data WHERE
title = 'CEO';
+——–+——-+
| salary | perks |
+——–+——-+
| 220000 | 55000 |
+——–+——-+
1 row in set (0.00 sec)
Actually, you don't need to know the previous salary explicitly. You can be cheeky and use arithmetic operators. Thus, the following statement would have done the same job without us knowing the original data beforehand.
UPDATE employee_data SET
salary = salary + 20000,
perks = perks + 5000
WHERE title='CEO';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Another progressive (???) step Bignet takes is changing the titles of Web Designer to Web Developer.
mysql> update employee_data SET
-> title = 'Web Developer'
-> WHERE title = 'Web Designer';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
It's important that you take a long hard look at the condition part in the statement before executing update, else you might update the wrong data. Also, an UPDATE statement without conditions will update all the data in the column in all rows!
Be very caferul.
Assignment:
1.Our CEO falls in love with the petite Web Developer, Anamika Pandit. She now wants her last name to be changed to 'Sharma'.
2.All Multimedia Programmers now want to be called Multimedia Specialists.
3.After his marriage, the CEO gives everyone a raise. Increase the salaries of all employees (except the CEO) by $10000.
Possible Answers:
1.mysql> update employee_data SET
-> l_name = 'Sharma'
-> WHERE l_name = 'Pandit';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Note: If there were other employees with 'Pandit' as last name, their entries would be updated too!. This is where our emp_id column comes is helpful since it contains unique values. It would have been better to use the value of emp_id instead of last names as in:
mysql> update employee_data SET
-> l_name = 'Sharma'
-> WHERE emp_id = 4;
2.mysql> update employee_data set
-> title = 'Multimedia Specialist'
-> where title = 'Multimedia Programmer';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
3.mysql> update employee_data set
-> salary = salary + 10000
-> where title != 'CEO';
Query OK, 20 rows affected (0.00 sec)
Rows matched: 20 Changed: 20 Warnings: 0