最近再移植一個從oracle轉mysql的項目，喜提一個報錯：
You can't specify target table 'A016' for update in FROM clause?
對應的程序代碼：
public void setCurrent(String setId, String pk, String userId) throws SysException {String[] sql = new String[2];sql[0] = "update " + setId + " set " + setId + "000='00900' where id in (select id from " + setId + " where subid='" + pk + "')";sql[1] = "update " + setId + " set " + setId + "000='00901' where subid='" + pk + "'";api.batchExecuteSql(sql);
}
sql[0]報的錯。在oracle sqlserver里都沒有錯。mysql不行。核心問題是mysql不允許在update的where語句里出現要更新的表。
針對這種情況只能先把值取出來，然后在執行。
public void setCurrent(String setId, String pk, String userId) throws SysException {String id=api.queryForString("select id from " + setId + " where subid='" + pk + "'");String[] sql = new String[2];sql[0] = "update " + setId + " set " + setId + "000='00900' where id ='"+id+"'";sql[1] = "update " + setId + " set " + setId + "000='00901' where subid='" + pk + "'";api.batchExecuteSql(sql);
}
)
)
