Update in SQL to implement multi-Table update

Update in SQL to implement multi-Table update

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 usage of the update statement in SQL Server, Oracle, and MySQL databases when updating multiple tables. I also tried the SQLite database, but I didn't know whether multi-Table update is supported or not. in this example: Use the gqdltks and bztks fields in the gdqlpj table to update the data of the same field name in landleveldata, the condition is that the geo_code field value in landleveldata is updated when it is equal to the lxqdm field value in gdqlpj.

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 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.