Sqlupdate multi-table joint update Method

The sqlupdate multi-table joint update method allows you to modify the Field Values of one table and update table one set table one according to the Field Values of one table as in associated queries. column table 2. column name from table 1, Table 2 where table 1. ID table 2. ID this article summarizes the usage of the Update statement in SQLServer, Oracle, and MySQL when updating multiple tables. I also tried the SQLite database, but I didn't know how to update multiple tables in SQL update.

You can modify the Field Values of one table and the association of the other table according to the Field Values of one table as in the associated query.
Update Table 1 set table 1. Column name = TABLE 2. Column name from table 1, Table 2 where Table 1. ID = TABLE 2. ID

This article summarizes the usage of the Update statement in SQL Server, Oracle, and MySQL when updating multiple tables.

The SQLite database is not successful. I wonder whether multi-Table update is not supported or not. In this example, we will use the table gdqlpj.

Gqdltks and bztks field data in to update the data of the same field name in landleveldata, if

When the GEO_Code field value in landleveldata is equal to the lxqdm field value in gdqlpj, it is updated.

SQL Server Syntax: UPDATE {table_name WITH (<table_hint_limited> [... n]) |

View_name | rowset_function_limited} SET {column_name = {expression | DEFAULT

| NULL} | @ variable = expression | @ variable = column = expression} [,... n]

{[FROM {<table_source>} [,... n] [WHERE <search_condition>]} | [

Where current of {[GLOBAL] cursor_name} | cursor_variable_name}]} [

OPTION (<query_hint> [,... n])]

SQL Server example: update a set a. gqdltks = B. gqdltks, a. bztks = B. bztks from

Landleveldata a, gdqlpj B where a. GEO_Code = B. lxqdm

Oracle Syntax: UPDATE updatedtable SET (col_name1 [, col_name2...]) = (SELECT

Col_name1, [, col_name2...] FROM srctable [WHERE where_definition])

Example of cancel: update landleveldata a set (a. gqdltks, a. bztks) = (B. gqdltks,

B. bztks from gdqlpj B where a. GEO_Code = B. lxqdm)

MySQL Syntax: UPDATE table_references SET col_name1 = expr1 [, col_name2 = expr2...]

[WHERE where_definition]

MySQL example: update landleveldata a, gdqlpj B set a. gqdltks = B. gqdltks, a. bztks =

B. bztks where a. GEO_Code = B. lxqdm

There are tables A and B with the following records:

Table
C1 c2
--------------
1 a1
2 a2
3 a3
8 a8

Table B
C1 c3
--------------
1 b1
2 b1
3 b3
10 b10

A. c1 is equal to B. c1. Use an SQL statement to update the value of A. c2 to B. c3.
------------------------
UPDATE
Set a. c2 = B. c3
From A, B
Where A. c1 = B. c1

UPDATE
Set a. c2 = B. c3
From A inner join B on A. c1 = B. c1

Note: update cannot be followed by multiple tables, but is followed by the from clause.

Access

Update multiple tables
UPDATE
SET a. t2 = B. t2
FROM x1 a, x2 B
WHERE a. t1 = B. t1

Perform some experiments:
X1 table:
T1 t2 f_id
A 1
B 2
C 3
A 4
B 5
C 6
X2 table
T1 t2 f_id
A 7
B 8
C 9

Test 1:
UPDATE
SET a. t2 = B. t2
FROM x1 a, x2 B
WHERE a. t1 = B. t1
Result: All six rows in Table x1 are updated. (At least left join Mode)

Test 2:
UPDATE B
SET B. t2 = a. t2
FROM x1 a, x2 B
WHERE a. t1 = B. t1
The result is updated with three rows, which are 4, 5, and 6. That is, use the three rows after x1 to update.

Test 3:
UPDATE
SET a. t2 = B. t2
FROM x1 a, x2 B
The result is changed to 9 in six rows.

Conclusion: Using this method, the two tables are not left join, right join, or inner join, but complete

The full connection method (where restricts the results of the full connection ). Update a column. There are multiple rows in the column value.

Only the last row takes effect.