MySQL Federated Multi-table update and delete

Multiple table Updates
in MySQL 3.23, you can use LIMIT # to make sure that only a given number of record rows is changed.

if an ORDER BY clause is used (supported from MySQL 4.0.0), the record row is updated in the specified order. This is actually only useful with LIMIT.

starting with MySQL 4.0.4, you can also perform an UPDATE operation with multiple tables:

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

Note: Multiple table UPDATE is not allowed to use ORDER by or LIMIT.


Multiple table Deletions
the first multi-table deletion format is supported starting from MySQL 4.0.0. The second multi-table deletion format is supported starting from MySQL 4.0.2.

A matching record row in a table that is listed only before the FROM or USING clause is deleted. The effect is that you want to delete from multiple tablesIn addition to the record lines, there can also be other tables for retrieval.

after the table name. * Just to be compatible with Access:

DELETE t1,t2 from T1,t2,t3 WHERE t1.id=t2.id and T2.id=t3.id

or
DELETE from T1,t2 USING t1,t2,t3 WHERE t1.id=t2.id and T2.id=t3.id

in the above case, we only delete the matching record lines from the T1 and T2 tables.

if an ORDER BY clause is used (supported from MySQL 4.0.0), the record row is deleted in the specified order. This is actually only useful with LIMIT. Examples are as follows:

DELETE from Somelog
WHERE user = ' Jcole '
ORDER by timestamp
LIMIT 1

This deletes the record line that matches the WHERE clause and is first inserted (determined by timestamp).

the Limit rows option for the DELETE statement is unique to MySQL, which tells the server that the maximum number of items that can be deleted before control is returned to the clientThe number of recording rows. This can be used to ensure that a specific DELETE command does not take a long time. You can simply re-use the DELETE command until the number of rows affected is less than the limit value.

starting with MySQL 4.0, you can specify multiple tables in the DELETE statement to remove special cases that depend on multiple tables from one tablerow of records. However, in a multiple-table delete, you cannot use ORDER by or LIMIT. Assuming there are two tables, TAB1,TAB2, the product name and the product price, and now I want to replace the TAB1 price with the TAB2 price, the statement I wrote is update tab1 set TAB1. Product Price =tab2. Product price where TAB1. Product name = TaB2. Product Name
The result is an error that indicates that the column prefix ' tab2 ' does not match the table name or alias used in the query. How should I write,

SQL statements are not supported for simultaneous updating of multiple tables.

It should be written.

Update TAB1 set TAB1. Product Price = (select TaB2. Product price from TAB2 where tab2. Product name = tab1. Product name) where TABL1. Product name in (select TAB2. Product Name From TAB2)


The where tab1 in the back. Product name in (select TAB2. Product name from TAB2) This guarantees that the TAB1 product will not be faulted if it is not recorded in TaB2.


In development, the database is swapped back and forth, and some key grammars are different, which is a headache for developers. This article summarizes the use of UPDATE statements in SQL Server,oracle,mysql three databases when updating multiple tables. I tried the SQLite database, Are not successful, I do not know whether to support multi-table updates or what.

In this example:

We will use the Gqdltks,bztks field data in table GDQLPJ to update data for the same field name in Landleveldata, if the Geo_code field value in Landleveldata is equal to the GDQLPJ field value in LXQDM .

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

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

MySQL Federated Multi-table update and delete