Delete row from 2 tables in Java

Question

Can anybody point what is wrong with my code? I have 2 tables and I am trying to delete a row from 2 tables. I was able to delete a row from 1 table but delete a row from 2 tables did not work. Here is my code:

String id = theId.getText();
String id2 = theId2.getText();
textArea.setText("");
try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/dealer", "root", "admin")) {
        stmt = connection.prepareStatement("DELETE FROM person, cars WHERE driverID = ?, carID = ?");
    if (stmt != null) {
        stmt.setString(1, id);
        stmt.setString(2, id2);
        stmt.executeUpdate();
    }

The error I got

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'WHERE driverID
= '1', carID = '1'' at line 1

Thank you so much!

Take a look at this question: http://stackoverflow.com/questions/3331992/how-to-delete-from-multiple-tables-in-mysql — Ernest Sadykov, Apr 17 '15 at 00:22

Saagar Elias Jacky · Answer 1 · 2015-04-17T01:18:51.590

2

Try this

String id = theId.getText();
String id2 = theId2.getText();
textArea.setText("");
try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/dealer", "root", "admin")) {
        stmt = connection.prepareStatement("DELETE person, cars FROM person, cars WHERE driverID = ? AND carID = ?");
        if (stmt != null) 
        {
            stmt.setString(1, id);
            stmt.setString(2, id2);
            stmt.executeUpdate();
        }

edited Apr 17 '15 at 01:18

answered Apr 17 '15 at 00:21

Saagar Elias Jacky

2,684
2
14
28

Thank you, but i don't know why i still get that error. Any idea? – John Apr 17 '15 at 00:28
You need to separate the WHERE clause filters using `AND` or `OR` – Saagar Elias Jacky Apr 17 '15 at 00:30
this `driverID` and `carID` columns, which table does they belong to? – Saagar Elias Jacky Apr 17 '15 at 00:52
driverID is from person table, carID from cars table – John Apr 17 '15 at 01:02
Does any of these columns occurs in both tables? Also, can you try this : `DELETE person, cars FROM person, cars WHERE person.driverID = ? AND cars.carID = ?` – Saagar Elias Jacky Apr 17 '15 at 01:05

MadProgrammer · Answer 2 · 2015-04-17T00:30:59.083

2

Your constraints should be more like...

WHERE driverID = '1' and  carID = '1'

I'd also highly recommend that you make use of a PreparedStatement

See Using PreparedStatement'sfor more details

edited Apr 17 '15 at 00:30

answered Apr 17 '15 at 00:21

MadProgrammer

343,457
22
230
366

1

I might be because there was another answer, which didn't show up in my browser till just now – MadProgrammer Apr 17 '15 at 00:30
Well I can confirm that you both answered at few seconds intervals. I don't think this is a real reason to downvote specially without leaving any comment; but anyway. – Jean-François Savard Apr 17 '15 at 00:31

Delete row from 2 tables in Java

2 Answers2