MySQL combined with multi-Table update and deletion, mysql combined update

MySQL combined with multi-Table update and deletion, mysql combined update
Multi-Table update
In MySQL 3.23, you can use LIMIT # to ensure that only the specified number of record rows is changed.

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

From MySQL 4.0.4, you can also perform an UPDATE operation that contains multiple tables:

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

Note: order by or LIMIT cannot be used for multi-Table UPDATE.


Delete multiple tables
The first multi-Table deletion format is supported since MySQL 4.0.0. The second multi-Table deletion format is supported since MySQL 4.0.2.

The matching record row in the table listed only before the FROM or USING clause is deleted. The result is that you want to delete multiple tables at the same time.In addition to record rows, other tables can also be used for retrieval.

After the table name, only. * is used for compatibility 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 matched record rows from Table t1 and table t2.

If an order by clause is used (supported since MySQL 4.0.0), the record row is deleted in the specified ORDER. This is actually useful only with LIMIT. Example:

Delete from somelog
WHERE user = 'jcol'
Order by timestamp
LIMIT 1

This will delete record rows that match the WHERE clause and are first inserted (determined by timestamp.

The LIMIT rows option of the DELETE statement is unique to MySQL and tells the server the maximum record that can be deleted before the control is returned to the client.Number of rows. This can be used to ensure that a specific DELETE command does not take too long. You can simply repeat the DELETE command until the number of affected record lines is less than the LIMIT value.

From MySQL 4.0, you can specify multiple tables in the DELETE statement to DELETE special situations that depend on multiple tables from one table.. However, order by or LIMIT cannot be used to delete multiple tables. Suppose there are two tables, tab1 and tab2, with the product name and price respectively. Now I want to replace the price of tab1 with the price of tab2, the statement I wrote is Update tab1 set tab1. product price = tab2. product price where tab1. product name = tab2. Product Name
The result is incorrect. The prompt is: the column prefix 'tab2' does not match the table name or alias used in the query. What should I do,

SQL statements do not support simultaneous update of multiple tables.

It should be written like this

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. product name in (select tab2. product name from tab2)" clause ensures that no error occurs if tab1 products are not recorded in tab2.


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:

We need to use the gqdltks and bztks fields in the gdqlpj table to update the data of the same field name in landleveldata, if the GEO_Code field value in landleveldata 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
{[GLOBAL] cursor_name} | cursor_variable_name}
]}
[OPTION (<query_hint> [,... n])]

SQL Server example:
Update
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
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 Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.