SQL two-table federated update

Tags: SQL federated Update Database casual Workplace original work, allow reprint, please be sure to use hyperlinks in the form of the original source of the article, the author information and this statement. Otherwise, the legal liability will be investigated. http://yukai.blog.51cto.com/1761494/372585

An update operation for the database is encountered today.

There are two tables in 1 databases (one of which is copied from another database, see my previous article), which corresponds to the company name, one of which is called (Mr.,miss.,mrs), and the other does not, and the table's title is imported into another table:

Sql:update Tb_company set Tb_company. Salutation1=tb_companyorg. Title from tb_company,tb_companyorg where tb_company.companyname_eng=tb_companyorg.company_name
This SQL statement functions as follows: Imports the title of the tb_companyorg table into the Salutation1 (appellation) field of the Tb_company table, corresponds to the company name of the two tables, and imports the 17w+ data at once

Reference link: http://www.cnblogs.com/ywkpl/archive/2008/03/11/1101276.html

Reference content:

Product (Pid,name,amount,nowamount): Identity, name, quantity already, current quantity
Trade (Id,pid,opertype,number): identification, product identification, type of operation (inbound: 1, out of stock: 0), quantity

Test data in the product table:

1	Apple	100	0
2	Orange	50	0

Test data in the trade table:

1	1	1	432
2	1	0	50
3	2	1	20
4	2	0	40
5	1	1	30
6	2	0	20

An SQL statement is now required to update the Nowmount value in the Product table
The statements are as follows:

Update p Set P.nowamount = P.amount+t.number
From Product as P, (select Pid,sum (case opertype if ' 0 ' then number* ( -1) Else number end) number
From trade group by PID) as T where T.pid=s.pid

Or

Update P
Set p.nowamount= T.number+p.amount
From Product as P
INNER JOIN (select PID, sum (case Opertype if ' 0 ' then number* ( -1) Else number end) as number from the trade group by PID) As T
On P.pid=t.pid

Execute the following product table like both:

1	Apple	100	512
2	Orange	50	10