Shulou(Shulou.com)06/01 Report--

Friends with some SQL foundation must have heard of "cross-table query". What is cross-table update?

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 Xiao Duan was always repairing immortals behind him, he thought of asking him to help polish 😜, so he sent it to him, and then came back 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. C1SET T1.C2 = T2.C2, T2.C3 = exprWHERE 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, followed by 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 column in the T1 or T2 table to be updated, and 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, T2SET T1.c2 = T2.c2, T2.c3 = exprWHERE 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 T2INNER JOIN T2 ON T1.C1 = T2.C1SET T1.C2 = T2.C2, T2.C3 = exprWHERE 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.performanceSET salary = 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 piglets 🐷 to eat zha, and two more googa

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.performanceSET salary = salary + salary * 0.015WHERE merits.percentage IS NULL

At this point, the salary increase of the new employees has also been done, and brother Gong went home gloomily for the Spring Festival because he did not understand the knowledge thoroughly.

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.