MySQL cross-table query and cross-table update

Friends who have some basic knowledge of SQL must have heard of "cross-table query", but what is cross-table update?

background

The project has imported a new batch of personnel data. Some of these people's department names have changed, and some of their contact information has changed. Let's call this table

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

Silly SQL is killed instantly

Without thinking, I wrote the following SQL

Seeing DBA Xiao Duan behind me practicing, I thought of asking him to help polish it 😜, so I sent it to him, and then it came back like this:

I was stunned when I saw this SQL statement. How could it be written like this? Under the merciless ridicule, I fell to the ground with a "KO". You have to die knowingly. We have to find out what's going on.

Mysql Update Join

We often use join to query rows from 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 also use the JOIN clause in the UPDATE statement to perform cross-table updates. The syntax is as follows:

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 primary table (T1) and the table (T2) to which you want the primary table to be joined. Note that you must specify at least one table after the UPDATE clause. Next, specify the type of join you want to use, either INNER JOIN or LEFT JOIN, and the join predicate. The JOIN clause must appear after the UPDATE clause (everyone knows this). Then, assign new values ​​to the columns in the T1 or T2 table to be updated. Finally, specify a condition in the WHERE clause to limit the rows to those to be updated.

If you follow the update syntax, you will find that there is another syntax that can also complete cross-table updates

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 equivalent to the following:

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

My personal suggestion is to add the inner join keyword, which will make the code more readable and smoother. What do you think?

I saw this while joking around, and I think it's a soul translation

Talk is cheap, show me the code

Update Join Example

It’s the end of the year, and it’s time to evaluate performance, that thing called KPI (do you have it?). I heard that salaries will be adjusted based on KPI. There are two tables

The first table is "employees"

The table creation statement is as follows:

create table employees
(
  employee_id bigint auto_increment comment 'Employee ID, primary key',
  employee_name varchar(50) null comment 'Employee name',
  performance int(4) null comment 'Performance score 1, 2, 3, 4, 5',
  salary float null comment 'Employee salary',
  constraint employees_pk
    primary key (employee_id)
)
comment 'employee table';

The second table is "merits-performance dictionary table"

The table creation statement is as follows:

create table merits
(
  performance int(4) null,
  percentage float null
)
comment 'Performance dictionary table';

Generate some simulated data first

-- Initialize the performance dictionary data INSERT INTO merits(performance, percentage)
VALUES (1, 0),
    (2, 0.01),
    (3, 0.03),
    (4, 0.05),
    (5, 0.08);


-- Initialize data in employee table INSERT INTO employees(employee_name, performance, salary)
VALUES ('Gong Ge', 1, 1000),
    ('Xiao Duanzong', 3, 20000),
    ('Adult', 4, 18000),
    ('Commander', 5, 28000),
    ('Old Six', 2, 10000),
    ('Romon', 3, 20000);

Salary adjustment rules:

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

Write update statements according to salary adjustment rules:

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

Gong Ge's performance was not good, so he didn't get a raise...

Three horizontal lines, one vertical line, and one cuckoo, four little pigs come to eat zha, cuckoo cuckoo, two more come

Near the end of the year, two new colleagues joined the company, but the company's annual performance evaluation has been completed, so the new employees' performance is NULL

INSERT INTO employees(employee_name, performance, salary)
VALUES ('冯大', NULL, 8000),
    ('冯二', NULL, 5000);

If the new employee does a good job, he or she should also get a 1.5% salary increase. If we still use UPDATE INNER JOIN , it is impossible to complete the update statement above because the conditional equality does not hold. This is when we need to use UPDATE LEFT JOIN .

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

At this point, the salary increase for new employees was also completed. Gong Ge went home to celebrate the New Year in shame because he did not have a thorough understanding of the knowledge points.