mysql在执行UPDATE 或者DELETE 时,同一张表必须要将子查询包装之后再查。否则会报错:Table ‘xxx’ is specified twice。例如:
DELETEFROM MYTABLE WHERE ID IN (SELECT ID FROM MYTABLE WHERE OTHERCOL=0) 报错
DELETEFROM MYTABLE WHERE ID IN
(SELECT * FROM (SELECT ID FROM MYTABLE WHERE OTHERCOL=0) AS TEMP) 正确
mysql派生出来的子查询结果必须有自己的别名,否则会报错:Every derived table must have its own alias。例如:
<!-- 这种写法对于mysql正确,对于oracle报错--><updateid="updateTaProductSalesAllot">
update ta_product_sales_allot t set
t.PRODUCT_CODE = #newProductCode#
where t.PRODUCT_CODE = #oldProductCode#;
</update>