SQL Update multi-table joint Update method, sqlupdate joint Update

SQL Update multi-table joint Update method, sqlupdate joint Update

Sometimes we need to update the data in multiple tables at the same time, then we need to use the following method:

(1) sqlite multi-Table update Method

Copy codeThe Code is as follows:
//----------------------------------
Update t1 set col1 = t2.col1
From table1 t1
Inner join table2 t2 on t1.col2 = t2.col2

This is a very simple batch update statement that is not supported by SQL Server in sqlite.

Sqlite can be converted to the following syntax:

Copy codeThe Code is as follows:
Update table1 set col1 = (select col1 from table2 where col2 = table1.col2)

Update ta_jbnt_tzhd_pht_Area_xiang set t1 = (select sys_xzqhdm.name from sys_xzqhdm
Where t2 = sys_xzqhdm.code)

(2) SQL Server multi-Table update Method

Copy codeThe Code is as follows:
//----------------------------------
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:

Copy codeThe Code is as follows:
Update a set a. gqdltks = B. gqdltks, a. bztks = B. bztks from
Landleveldata a, gdqlpj B where a. GEO_Code = B. lxqdm

Access Database multi-Table update Method

Copy codeThe Code is as follows:
X = "update" + DLTB + "a inner join tbarea2 B on. objectid = B. FID set. "+ fd_dltb_xzdwmj +" = B. area_xzdw,. "+ fd_dltb_lxdwmj +" = B. area_lxdw ";
SQLList. Add (x );

(3) Oracle multi-Table update Method

Oracle Syntax:
Copy codeThe Code is as follows:
UPDATE updatedtable SET (col_name1 [, col_name2...]) = (SELECT
Col_name1, [, col_name2...] FROM srctable [WHERE where_definition])

Example of cancel:

Copy codeThe Code is as follows:
Update landleveldata a set (a. gqdltks, a. bztks) = (select B. gqdltks,
B. bztks from gdqlpj B where a. GEO_Code = B. lxqdm)

(4) MySQL multi-Table update Method

MySQL Syntax:

Copy codeThe Code is as follows:
UPDATE table_references SET col_name1 = expr1 [, col_name2 = expr2...]
[WHERE where_definition]

MySQL example:

Copy codeThe Code is as follows:
Update landleveldata a, gdqlpj B set a. gqdltks = B. gqdltks, a. bztks =
B. bztks where a. GEO_Code = B. lxqdm

SQL statement UPDATE

The statement has no syntax error. What database do you use?
Some early databases, or very simple databases, do not support multi-table queries, such as access97.
Is there an error during execution? Can you output the log after execution?

SQL update multi-Table update

Update table A set Table A. field K = 'pass' from Table A, Table B where Table A. Field L = Table B. Field L and Table B. Field M> = 60