Cross-table queries are common. What are cross-table updates?

If you have a little SQL foundation, you must have heard of “cross-table query”. What is cross-table update?

background

The project imported the data of a group of people, whose department names and contact information had changed, temporarily named as

T_dept_members, another table in the system, T_user_info, records people information. The requirement to update changes in T_DEPt_members to the T_USER table is known as “cross-table update”

Han B SQL was killed in seconds

Without thinking out of the house to write the following SQL

After seeing that DBA is always repairing immortals, I wanted him to help me polish 😜, so I sent it to him, and then came back like this:

​

I was stunned to see this SQL statement, and could write this, under the merciless ridicule, A KO I fell directly. I know. We need to find out what’s going on here

Mysql Update Join

We often use joins to query rows in tables 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. The syntax looks like this:

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

Let’s elaborate on the syntax above:

• First, after the UPDATE clause, specify the primary table (T1) and the table (T2) to which you want the primary table to join. Note that at least one table must be specified after the UPDATE clause
• Next, specify the type of JOIN you want to use, namely INNER JOIN or LEFT JOIN, and JOIN predicates. JOIN clause must appear after UPDATE clause
• The new values are then assigned to the columns in the T1 or T2 tables to be updated
• Finally, specify a condition in the WHERE clause to limit rows to those to be updated

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

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

The syntax above implicitly uses the inner join keyword, 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
Copy the code

Personal advice to addinner joinKeyword, it’s more readable, it’s silky, don’t you think?

I felt what I saw, and I thought it was soul translation

Talk is cheap, show me the code

Update the Join example

It’s the end of the year, and it’s time to evaluate performance again. It’s the thing called KPI (do you have it?). It’s said that salary will be adjusted according to KPI. There are two tables

The first table “employees-employees table”

The construction sentences are 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 scores 1,2,3,4,5',
	salary float null comment 'Employee salary'.constraint employees_pk
		primary key (employee_id)
)
comment 'Staff list';
Copy the code

The second table “Poets-Performance Dictionary”

The construction sentences are as follows:

create table merits
(
	performance int(4) null,
	percentage float null
)
comment 'Performance Dictionary';
Copy the code

Sir Into some simulation data

-- Performance dictionary initializes data
INSERT INTO merits(performance, percentage)
VALUES (1.0),
       (2.0.01),
       (3.0.03),
       (4.0.05),
       (5.0.08);


-- Employee table initializes data
INSERT INTO employees(employee_name, performance, salary)
VALUES ('the elder brother of the arch.1.1000),
       ('Small segment total'.3.20000),
       ('adults'.4.18000),
       ('the commander'.5.28000),
       ('the old six'.2.10000),
       ('receive'.3.20000);
Copy the code

Salary adjustment Rules:

Old salary + (Old salary * adjusted percentage of current performance)

Update statement:

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

Arch elder brother performance is bad, did not give increase salary……

Three horizontal and one vertical one goo ga, four little pigs 🐷 to eat zha, goo ga goo ga two more

Near the end of the year, two new colleagues came to the company, but the annual performance of the company has been evaluated, so the performance of the new employee is NULL

INSERT INTO employees(employee_name, performance, salary)
VALUES ('von big'.NULL.8000),
       ('feng 2'.NULL.5000);
Copy the code

New employees who do a good job should get a 1.5% raise. If we were to use UPDATE INNER JOIN, the above statement would be impossible to complete because the condition equality is not true, so we would use UPDATE LEFT JOIN instead

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

To here, the new employee’s salary work is also done, arch elder brother due to knowledge understanding is not thorough, dejectly home for the New Year

---

• If you happen to know that, please give it a thumbs up.

• If you already know this, please leave a comment and hiss.

• If you have a big year-end bonus, I hope you can go one step further in 2020

• If you’re like me and you don’t get a year-end bonus, don’t lose heart. Let’s make progress together

The flu is serious 😷, be careful during the Spring Festival travel

Personal blog: https://dayarch.top

Welcome to continue to pay attention to the public account: “One soldier of The Japanese Arch”

• Cutting-edge Java technology dry goods sharing
• Efficient tool summary | back “tool”
• Interview question analysis and solution
• Technical data to receive reply | “data”

To read detective novel thinking easy fun learning Java technology stack related knowledge, in line with the simplification of complex problems, abstract problems concrete and graphical principles of gradual decomposition of technical problems, technology continues to update, please continue to pay attention to……

---