Shulou(Shulou.com)06/01 Report--

1. Update the contents of Table B by taking data from Table An in MySQL

For example, you want to update some column properties in the table data, but the content source of the modified properties is from the table chanpin. The select keyword that is not displayed in the SQL language

Update data dreachchanpin c set d.zhuleiqic.zhuleired.xiaoleired.fenxiangc.fenxiangprocyd.zhuanyeprocligend.jiliangc.jilianglid.gspowerd.xzgscorrecd.bjgs.bjgs= (c.zgs*d.jdxs*d.jishu*d.xs1*d.xs2*d.xs3), d.wygswatches d.bjgs.bjgsminutes? Where d. Idling? And C. Chanpinghouse? And c.fenleigh? "

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

What's a little annoying about MySQL is that its select from cannot 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 name of the table to be updated is the same as that of the query. Sql execution Times 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 queries and updates in a select-nested way, as follows:

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

Notice the following in SQL above:

(select * from TBCP_SELLER) as x

As x must have, or an error will be reported: Every derived table must have its own alias (each derived table must have its own alias)

III. The difference between temporary tables and views

Temporary watch

Temporary tables are tables established in the temporary folder of the system. If used properly, they can perform various operations like regular tables and are automatically released when VFP exits. Creating MySQL temporary tables is easy, adding the TEMPORARY keyword Sql code to normal CREATE TABLE statements:

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 looks at the data in the database from a specific perspective.

From within the database system, a view is a virtual table defined by a query made up of SELECT statements.

From within the database system, the view is made up of data from one or more tables

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.