Course Module 4. Working with databases - Lecture: Changing data in a table

4.1 UPDATE SET statement

If you need to change existing records in the table, then for this we use the operator UPDATE.

On the one hand, the operator UPDATEis similar to the operator INSERT, because we need to specify new values for the columns. And on the other hand, on the operator SELECT, because it allows you to work immediately with data groups.

Important! The purpose of the operator UPDATEis to change the data not in one row of the table, but in all rows that meet a certain condition. And if you make a small mistake, you can easily lose all the data in the table.

General view of the request:

UPDATE table SET
  	column 1 = expression 1,
  	column 2 = expression 2,
  	column 3 = expression 3
WHERE condition;

Let's write a query that will increase the salary of all our employees by 100. Our query will look like this:

UPDATE employee SET
  	salary = salary+100;

And now for everyone who has a salary of less than 50K, we will also increase it by 20%. Request example:

UPDATE employee SET
  	salary = salary*1.2
WHERE salary<50000;

And of course, we will increase the salary of the director by 2 times, how could it be without him:

UPDATE employee SET
  	salary = salary*2
WHERE id=4;

4.2 Complex data change scenarios

If you decide to change data in several tables at the same time, or when you change data in one table, you need to fill it with data from another, or simply somehow use data from another table in the process of changing the first one, then surprises await us.

Let's try to change the level of all users in the user table that exist in the employee table. For simplicity, we will check the coincidence of users by name.

How would we like to write this query:

UPDATE user SET
  	level = 80,
WHERE user . name IN ( list of names from employee table );

Well, let's add a list of names from the employee table to this query:

UPDATE user SET
  	level = 80,
WHERE user.name IN (select name from employee);

Interesting! The SQL query itself for getting the names turned out to be even shorter than the description of the task. The creators of SQL did not eat their bread in vain.

Forced to upset you -this approach will not work. First you need to join these tables, it will look something like this:

UPDATE table 1, table 2 SET
  	column 1 = expression 1,
  	column 2 = expression 2,
  	column 3 = expression 3
WHERE table 1.id = table 2.ref_id;

Let's make it a little more difficult. You need to set the level to 80 only for good employees whose salary will be more than 50k. The complete solution will look like this:

UPDATE user, good_employees SET
  	user.level = 80
WHERE user.name = good_employees.name;

Such a join of tables is actually INNER JOIN, so that in the end only users who have a match by name in the second table will remain.

But we still need to somehow select good employees, so let's add some more code:

SELECT * FROM employee WHERE salary >= 50000

And now we substitute this table in our final query:

UPDATE user,
    (SELECT * FROM employee WHERE salary >= 50000) good_emps
SET
    user.level = 80
WHERE user.name = good_emps.name;

Live with it now.