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