background

The project imports a new set of people whose department names and contact information have changed. Let’s call this table TdeptMembers. Another table tUserInfo records people’s information.

The requirement to update changes in tdeptmembers to the T_USER table is known as “cross-table updates.

Without thinking out of the house to write the following SQL

When I saw the DBA repairing immortals behind me, I thought I would ask him to help me polish it, so I sent it to him, and then came back like this:

I was shocked to see this SQL statement, but I have to find out what happened

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 conditionCopy 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 you must specify at least one table 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 conditionCopy 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 conditionCopy the code

Update the Join example

It’s the end of the year, and it’s time to evaluate performance again. I heard that salary will be adjusted according to KPI. There are two tables

The first table “employees-employees table”

The second table “Poets-Performance Dictionary”

The following data

Salary adjustment rule: Original salary + (Original salary * percentage of salary adjustment corresponding to current performance)

Update statement:

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

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 (' feng da ', 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

By here, the new employee’s salary increase is also done

From: https://dayarch.top/p/mysql-cross-table-update.html