Description:
java.sql.SQLException: You can't specify target table 'tbl_request' for update in FROM clause
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2921)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1570)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2978)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2902)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:930)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1159)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1076)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1061)
at com.singtel.RequestDAO.dbErase(RequestDAO.java:218)
at com.singtel.RequestEraser$RequestEraserTask.run(RequestEraser.java:67)
at java.util.TimerThread.mainLoop(Unknown Source)
at java.util.TimerThread.run(Unknown Source)
How to repeat:
delete from tbl_request
where exists
(
SELECT CORE.RequestID, SetA.Total as Total, SetB.TotalC as TotalC
FROM tbl_Request as CORE
LEFT JOIN
(
SELECT RequestID, count(*) as Total
FROM tbl_Contact
GROUP BY RequestID
) as SetA
ON CORE.RequestID = SetA.RequestID
LEFT JOIN
(
SELECT RequestID, count(*) as TotalC
FROM tbl_Contact
WHERE status = 'C'
GROUP BY RequestID
) as SetB
ON CORE.RequestID = SetB.RequestID
WHERE tbl_request.RequestID = Core.RequestID
and SetA.Total = SetB.TotalC
)
