A method of SQL Update multi-table joint updating

A method of SQL Update multi-table joint updating

You can modify the field values of one table and the other table associations based on the values of one table's fields, like an associated query
Update table one set table one. Column name = Table two. column name from table one, table two where table one. id = table Two. ID

This article summarizes the use of the UPDATE statement in the SQL Server,oracle,mysql three database tutorials when updating multiple tables. I'll try.

The SQLite database, did not succeed, I do not know whether to support multiple table updates or what. In this case: we're going to use the table GDQLPJ

Gqdltks,bztks field data in the Landleveldata to update data in the same field name in the

The Geo_code field value in Landleveldata is updated when the LXQDM field value in GDQLPJ is equal.

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])

Oracel Example: Update Landleveldata a set (A.gqdltks, A.bztks) = (select 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, and their records are as follows:

A table
C1 C2
--------------
1 A1
2 A2
3 A3
8 A8

Table B
C1 C3
--------------
1 B1
2 B1
3 B3
Ten B10

A.C1 is equal to B.C1, with an SQL statement, the value of A.C2 is updated to b.c3
------------------------
UPDATE A
SET A.C2 =b.c3
From A, B
where A.C1=B.C1

UPDATE A
SET A.C2 =b.c3
From A inner join B on A.C1=B.C1

Note: After update, you cannot follow multiple tables, but follow the FROM clause

Access

Updating multiple tables
UPDATE A
SET a.t2 = b.t2
From X1 A, X2 b
WHERE a.t1 = b.t1

To make 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 A
SET a.t2 = b.t2
From X1 A, X2 b
WHERE a.t1 = b.t1
Result: All 6 rows in the X1 table were updated. (at least the way of the left connection)

Test 2:
UPDATE b
SET b.t2 = a.t2
From X1 A, X2 b
WHERE a.t1 = b.t1
The result is an update of three rows with a value of 4,5,6. It is updated with the three lines behind the X1.

Test 3:
UPDATE A
SET a.t2 = b.t2
From X1 A, X2 b
As a result, all six lines changed to 9.

Conclusion: In this way, two tables are not a LEFT join or a right join or a inner join, but rather an end

The full connection (where the result of the full connection is limited). An update operation on a column that has more than one row in the value of the column

, only the last line takes effect