王新阳

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 customer SET customer.amount=(SELECT 
SUM(works_renewals.contractamount) FROM works_renewals 
WHERE customer.cid=works_renewals.cid)

UPDATE customer 
SET customer.photo = COALESCE(
    (SELECT works_renewals.photo FROM works_renewals 
    WHERE works_renewals.customid=customer.id),
    'http://abc.com/photo.jpg'  -- 当子查询返回 NULL 时使用这个默认值
);

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

当表A.name=表B.name时，用 A.id 填充 B.aid

UPDATE tableA
JOIN tableB ON tableA.name = tableB.name
SET tableB.aid = tableA.id;