CODEWITHSUNDEEP
javamysql
user178798799998
user178798799998

Reputation: 33

New to mysql, query throwing wrong syntax

I am trying to pass this query from my Java application but it is saying I have an error in the UPDATE line, but i cant find what is wrong? can you spot see what I am doing wrong?

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+"';");

Upvotes: 0

Views: 80

Answers (1)

Jacob
Jacob

Reputation: 43309

You can't use a WHERE clause with ON DUPLICATE KEY UPDATE. It is updating the duplicate. If you have a UNIQUE index on (periodEndingDate, employeeID), it is already updating the correct row without the where clause. Also, lose the tablename and the SET in the Update clause.

MySQL Docs on ON DUPLICATE KEY UPDATE

Upvotes: 2

Related Questions