javaprogrammer javaprogrammer - 4 months ago 14
SQL Question

database not updating for some reason

This sql query is not updating the database, instead returning error. Any suggestions?

PreparedStatement ps9 = con.prepareStatement("INSERT INTO payroll_system.payslip(expense_claims) WHERE employeeID=? SELECT SUM(expense) FROM payroll_system.expense_master");
ps9.setString(1, employee_id);
ps9.executeUpdate();


payslip table:
enter image description here

expense_master table:

enter image description here

Answer
Insert don't have where clause  
"INSERT INTO payroll_system.payslip(expense_claims) 
 WHERE employeeID=? 
 SELECT SUM(expense) FROM payroll_system.expense_master"

eventually you are looking for update?

or use

     "INSERT INTO payroll_system.payslip(expense_claims) 
      SELECT SUM(expense) FROM payroll_system.expense_mast"

for update

 PreparedStatement ps9 = con.prepareStatement("UPDATE payroll_system.payslip 
     SET expense_claims = (SELECT SUM(Expense) 
     FROM payroll_system.expense_master 
     WHERE employeeID=?) WHERE employeeID=?");

     ps9.setString(1, employee_id);
     ps9.setString(2, employee_id);
     ps9.executeUpdate();
Comments