So i have two tables: locations and employees i want locations_id to be the same in employees.locations_id, I am trying to make it all in one statement the erros is this You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO employees(employees_id, locations_id) VALUES('e1598','')' at line 1
String sql = " INSERT INTO locations( locations_id , username, password, id, type_of_id, first_name, last_name, phone, email, date_of_birth, address, sex ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)"
**Error here --->** + "INSERT INTO employees(employees_id,locations_id) VALUES (?,SELECT locations_id FROM locations INNER JOIN employees ON locations.locations_id =employees.locations_id)";
try {
MicroModelGUI micro = new MicroModelGUI();
PreparedStatement consulta = micro.connection.prepareStatement(sql);
consulta.setString(1, tflid.getText());
consulta.setString(2, tfuser.getText());
consulta.setString(3, tfpass.getText());
consulta.setString(4, tfid.getText());
consulta.setString(5, tftoid.getText());
consulta.setString(6, tffirst.getText());
consulta.setString(7,tflast.getText());
consulta.setString(8,tfphone.getText());
consulta.setString(9,tfemail.getText());
consulta.setString(10,tffdn.getText());
consulta.setString(11,tfaddress.getText());
consulta.setString(12,tfsex.getText());
consulta.setString(13,tfeid.getText());
int resultado = consulta.executeUpdate();
You should be using an INSERT INTO ... SELECT here:
INSERT INTO employees (employees_id, locations_id)
SELECT ?, l.locations_id
FROM locations l
INNER JOIN employees e ON l.locations_id = e.locations_id;
To the ? placeholder you would bind a value from your Java code. Note that while your version of SQL might support putting a scalar subquery into a VALUES clause, it is likely that your exact version would cause an error, as it would return more than one row.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments