Update in SQL to implement multi-Table update

The update in SQL implements Multi-Table update during development, and the database is switched back and forth, and some key syntaxes are different. This is a headache for developers. this article summarizes the usage of the Update statement in three databases: SQLServer, Oracle, and MySQL when updating multiple tables. I also tried the SQLite database, but it was not successful. I do not know that multi-Table update is not supported.

The update in SQL implements Multi-Table update during development, and the database is switched back and forth, and some key syntaxes are different. This is a headache for developers. this article summarizes the usage of the Update statement in SQL Server, Oracle, and MySQL databases when updating multiple tables. I also tried the SQLite database, but it was not successful. I do not know that multi-Table update is not supported.

Update in SQLImplementationMultiple tablesUpdate

During development, the database is switched back and forth, and some key syntaxes are different. This is a headache for developers. This article summarizes the Update statement.UpdateWhen multiple tables are used in SQL Server, Oracle, and MySQL databases, I also tried the SQLite database and did not succeed. I do not know that multiple tables are not supported.UpdateIn this example, we need to use the gqdltks and bztks fields in the gdqlpj table.UpdateIf the GEO_Code field value in landleveldata is the same as the lxqdm field value in gdqlpjUpdate.

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) = (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 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,ImplementationA. c2 ValueUpdateB. 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.