两表组合更新示例
MSSQL:
Update [INVOICE] SET INVOICE.BUREAU_NO=TP_USER.BUREAU_NO
FROM [INVOICE] JOIN [TP_USER] ON INVOICE.TAXPAYER_ID=TP_USER.TAXPAYER_ID
WHERE INVOICE.TAX_FLAG='正常' And INVOICE.TAX_DATE<>'1900-1-1'
以上不适用于access
MYSQL:
Update [INVOICE] SET INVOICE.BUREAU_NO=TP_USER.BUREAU_NO
FROM [INVOICE] JOIN [TP_USER] ON INVOICE.TAXPAYER_ID=TP_USER.TAXPAYER_ID
WHERE INVOICE.TAX_FLAG='正常' And INVOICE.TAX_DATE<>'1900-1-1'
以上不适用于access
MYSQL:
UPDATE customer SET customer.amount=(SELECT SUM(works_renewals.contractamount) FROM works_renewals WHERE customer.cid=works_renewals.cid)
MYSQL
UPDATE book SET price=100 WHERE id IN (SELECT id FROM book AS a WHERE a.price=99)
错误代码： 1093
You can't specify target table 'book' for update in FROM clause
mysql同表组合更新时，需要把SELECT出来的数据放到临时表中。SELECT的表和临时表都要有别名。正确语句：
UPDATE book SET price=100 WHERE id IN (SELECT b.id FROM (SELECT id FROM book AS a WHERE a.price=99) AS b)
MySQL自表更新示例
update
wxy_enum, (select tmp.id, tmp.addfield1 from wxy_enum as tmp) tmp2
set wxy_enum.parentid=tmp2.id
where wxy_enum.addfield2=tmp2.addfield1 and wxy_enum.rootid>0
