java - mysql新手，查询抛出错误语法

Question

我试图从我的 Java 应用程序传递此查询，但它说我在 UPDATE 行中有错误，但我找不到问题所在？你能看出我做错了什么吗？

int rs = st.executeUpdate("INSERT INTO timesheet (employeeID, statusCode, periodEndingDate, departmentCode, minutesMon,"+
            "minutesTue, minutesWed, minutesThu, minutesFri, minutesSat, minutesSun)"+
            " VALUES ('"+empID+"','"+statusCode+"','"+periodEndingDate+"','"+departmentCode+"','"+minutesMon+"','"+
             minutesTue+"','"+minutesWed+"','"+minutesThu+"','"+minutesFri+"','"+minutesSat+"','"+minutesSun+"')"+
             " ON DUPLICATE KEY UPDATE timesheet SET statusCode='"+statusCode+"', periodEndingDate='"+periodEndingDate+"', departmentCode='"+departmentCode+"',"+
            " minutesMon = '"+minutesMon+"', minutesTue='"+minutesTue+"', minutesWed='"+minutesWed+"', minutesThu='"+minutesThu+"', minutesFri='"+minutesFri+"',"+
            " minutesSat='"+minutesSat+"', minutesSun='"+minutesSun+"' WHERE periodEndingDate='"+periodEndingDate+"' AND employeeID='"+empID+"';");

Answer 1

您不能将 WHERE 子句与 ON DUPLICATE KEY UPDATE 一起使用。它正在更新副本。如果您在 (periodEndingDate, employeeID) 上有一个 UNIQUE 索引，则它已经在更新正确的行，而无需使用 where 子句。此外，还会丢失 Update 子句中的表名和 SET。

MySQL Docs on ON DUPLICATE KEY UPDATE