Combination of Update and select in MySQL to operate single table multi table and the difference between view and temporary table

1. MySQL updates the contents of table B using data fetched from table A

For example, I want 1 column attributes in the update table data, but the content source of the modified attributes is from the table chanpin. Do not show select keywords in SQL language

update data d,chanpin c set d.zhulei=c.zhulei,d.xiaolei=c.xiaolei,d.fenxiang=c.fenxiang,d.zhuanye=c.zhuanye,d.jiliang=c.jiliang,d.gs=c.zgs,d.xzgs=c.zgs,d.bjgs=(c.zgs*d.jdxs*d.jishu*d.xs1*d.xs2*d.xs3),d.wygs=d.bjgs,d.hzgs=? where d.id=? and c.chanpin=? and c.fenlei=?";

2. The tables of update and select in MySQL are the same table

MySQL is a bit annoying because its select from can't be followed by the table name of update, for example:

update TBCP_SELLER set status =( select STATUS from TBCP_SELLER where tbid=2011645303 ) where tbid=2011645303 

The table to be updated has the same name as the queried table, and the sql execution reports an error: You can 't specify target table' TBCP SELLER 'for update in FROM clause
To solve this problem, we can construct a view and implement query and update in select nesting, as follows:

update TBCP_SELLER set status =( select STATUS from ( select * from TBCP_SELLER )as x where tbid=2011645303 ) where tbid=2011645303 

Notice in SQL above:

( select * from TBCP_SELLER )as x

as x must have, otherwise an error will be reported: Every derived table must have its own alias (every derived table must have its own alias)

3. Differences between temporary tables and views

Temporary table

Temporary table is a table established in the temporary folder of the system. If used properly, it can perform various operations like ordinary table 1, and will be automatically released when VFP exits. It is easy to create an MySQL temporary table by adding the TEMPORARY keyword Sql code to the normal CREATE TABLE statement:

CREATE TEMPORARY TABLE tmp_table ( name VARCHAR(10) NOT NULL, value INTEGER NOT NULL )

CREATE TEMPORARY TABLE tmp_table ( name VARCHAR(10) NOT NULL, value INTEGER NOT NULL )

View

From the user's point of view, a view is to view the data in the database from a specific point of view.

From the inside of the database system, a view is a virtual table defined by a query composed of SELECT statements.

From the inside of the database system, a view is composed of data in one or more tables

From the outside of the database system, the view is just like a table 1, and all the operations that can be performed on the table can be applied to the view, such as query, insert, modify, delete and so on.