The difference between Update and select in MySQL for single and multiple tables, and views and temporary tables

1. Use data from table A to update the content of table B in MySQL

For example: you want to update some column attributes in the data table, but the content of the modified attributes comes from the chanpin table. The select keyword should not appear 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 table for update and select in MySQL is the same table

What is a bit annoying about MySQL is that its select from statement cannot be followed by the name of the table to be updated, 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 table to be queried. When SQL is executed, an error message is displayed: You can't specify target table 'TBCP SELLER' for update in FROM clause
To deal with this problem, we can construct a view and use nested select to implement query and update, as shown below:

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

Note the following in the SQL above:

( select * from TBCP_SELLER )as x

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

3. The difference between temporary tables and views

Temporary Tables

A temporary table is a table created in the system temporary folder. If used properly, it can be operated in various ways like a normal table and will be automatically released when VFP exits. Creating a MySQL temporary table is easy. Add the TEMPORARY keyword to the normal CREATE TABLE statement. Sql code:

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 a user's perspective, a view is a look at the data in the database from a specific perspective.

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

From the perspective of the database system, a view is composed of data from one or more tables.

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