Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to achieve cross-table update of mysql". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Background

A new batch of personnel data has been imported into the project. Some of these people's department names have changed, and some contact information has changed. Let's just call this table

T_dept_members, there is another table in the system, t_user_info records the personnel information. It is required to update the changed information in t_dept_members to the t_user table, which is called "cross-table update".

Han B SQL was killed directly.

The person who went out without thinking wrote the following SQL

Seeing that DBA Duan was always fixing the immortal behind him, he wanted him to help embellish him, so he sent it to him, and then came back with his hand like this:

I was shocked to see this SQL sentence. I was able to write like this. Under merciless ridicule, I fell down with a KO. You have to die. I understand. We have to find out what's going on.

Mysql Update Join

We often use join to query rows in a table that have (in the case of INNER JOIN) or may not have (in the case of LEFT JOIN) matching rows in another table.

Similarly, in MySQL, we can use the JOIN clause in the UPDATE statement to perform cross-table updates, which is what the syntax looks like:

UPDATE T1, T2, [INNER JOIN | LEFT JOIN] T1 ON T1.C1 = T2. C1 SET T1.C2 = T2.C2, T2.C3 = expr WHERE condition

Let's explain the above syntax in detail:

First, after the UPDATE clause, specify the main table (T1) and the table to which you want the primary table to join (T2). Note that at least one table must be specified after the UPDATE clause

Next, specify the type of join you want to use, that is, INNER JOIN or LEFT JOIN, and the join predicate. The JOIN clause must appear after the UPDATE clause (as we all know)

Then, assign the new value to the columns in the T1 or T2 table to be updated

Finally, specify a condition in the WHERE clause to restrict the row to the row to be updated

If you follow the update syntax, you will find that there is another syntax that can also be updated across tables.

UPDATE T1, T2 SET T1.c2 = T2.c2, T2.c3 = expr WHERE T1.c1 = T2.c1 AND condition

The above syntax actually implicitly uses the inner join keyword, which is exactly the same as the following:

UPDATE T1 T2 INNER JOIN T2 ON T1.C1 = T2.C1 SET T1.C2 = T2.C2, T2.C3 = expr WHERE condition

Personally, I suggest adding the inner join keyword to make it more readable and silky, don't you think?

What I saw when I touched the fish, I thought it was a soul translator.

Talk too cheap, show your size (Talk is cheap,show me the code)

Update Join example

At the end of the year, it's time to evaluate the performance again, that is, the thing called KPI (do you have any)? I heard that the salary will be adjusted according to KPI. There are two tables.

The first form "employees- employee Table"

The statement of the table is as follows:

Create table employees (employee_id bigint auto_increment comment 'employee ID, key', employee_name varchar (50) null comment 'employee name', performance int (4) null comment 'performance score 1, 2pyrm, 4pint 5pm, salary float null comment' employee salary, constraint employees_pk primary key (employee_id)) comment 'employee form'

The second table "merits- performance Dictionary Table"

The statement of the table is as follows:

Create table merits (performance int (4) null, percentage float null) comment 'performance Dictionary Table'

Sir, turn into some simulation data.

-- performance dictionary initialization data INSERT INTO merits (performance, percentage) VALUES (1,0), (2,0.01), (3,0.03), (4,0.05), (5,0.08) -- employee table initialization data INSERT INTO employees (employee_name, performance, salary) VALUES ('Gong GE', 1, 1000), ('small total', 3, 20000), ('adult', 4, 18000), ('commander', 5, 28000), ('Lao Liu', 2, 10000), ('Romon', 3, 20000)

Adjust the pay rules:

Original salary + (original salary * percentage of salary adjustment corresponding to current performance)

Write the update statement according to the compensation adjustment rules:

UPDATE employees INNER JOIN merits ON employees.performance = merits.performance SET salarysalary = salary + salary * percentage

Brother Gong's performance was not good and he didn't give him a raise.

Three horizontal, one vertical and one googa, four little pigs? Come to eat zha, googa and googa again.

Towards the end of the year, there are two new colleagues in the company, but the company's annual performance has been evaluated, so the new employee performance is NULL.

INSERT INTO employees (employee_name, performance, salary) VALUES ('big', NULL, 8000), ('double', NULL, 5000)

The new employee is doing a good job and needs a 1.5% raise in salary. If we still use UPDATE INNER JOIN, it is impossible to do it according to the above update statement, because the conditional equation is not true, so we are going to use UPDATE LEFT JOIN.

UPDATE employees LEFT JOIN merits ON employees.performance = merits.performance SET salarysalary = salary + salary * 0.015 WHERE merits.percentage IS NULL

This is the end of the content of "how to implement cross-table updates in mysql". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.