Delete Rows in Two Tables with Java and SQL

Question

I have connected Java to SQL and want to delete two rows from different tables, but having issues with my Java Method. I am trying to delete from both tables because I can't delete ID from Table1 because it is referenced by table2. Any suggestions on fixing this?

public void delete() throws SQLException
    {
        DataConnection connection = new DataConnection();

        String query = " DELETE FROM " + Table1 + " WHERE ID = " + id + " AND " + " DELETE FROM " + Table2 + " WHERE ID = " + id;
        connection.updateData(query);
        connection.closeConnection();
    }

First delete from table2 and then table1.

Amit
– Amit

2014-04-26 16:31:58 +00:00
Commented Apr 26, 2014 at 16:31 — Amit
– Amit, Commented Apr 26, 2014 at 16:31

Karibasappa G C · Accepted Answer · 2014-04-26 16:36:13Z

1

See Always child references should be deleted first.

if there are any references to parent table then parent table can not be deleted.

but if the parent table has cascade delete then it can delete all child tables while deleting table.

in your case table1 is parent as it is referencing from one of the child table table2.

so delete table2 first then any other tables if they are also referencing table1's id.

then try to delete table1.

it should work.

let me know for any issues.

answered Apr 26, 2014 at 16:36

Karibasappa G C

2,7322 gold badges21 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ChaseHardin Over a year ago

okay I delete from table2, but then it still doesn't allow me to delete from table1. Any suggestions on an approach?

Karibasappa G C Over a year ago

is there any other reference table for table1 apart from table2 ?

Karibasappa G C Over a year ago

then you have to delete that table too otherwise you cant delete table1

babanin · Accepted Answer · 2014-04-26 16:39:03Z

1

Make your method reusable:

public void delete(String table, long id) throws SQLException {
    DataConnection connection = new DataConnection();

    String query = " DELETE FROM " + table + " WHERE ID = " + id;
    connection.updateData(query);

    connection.closeConnection();
}

Then just invoke:

delete(Table1, id);
delete(Table2, id);

But it depends on cost of new connection creation.

answered Apr 26, 2014 at 16:39

babanin

3,6193 gold badges23 silver badges42 bronze badges

1 Comment

ChaseHardin Over a year ago

Hi @Ivan Babanin where does the delete(Table1, id) go? I am creating this so a user can delete without writing code.

Amit · Accepted Answer · 2014-04-26 17:01:35Z

1

Execute queries in this order one by one, you can't do it in one single query -

String s2 =  " DELETE FROM table2 WHERE ID = " + id;
// Execute s2 here

String s1 = "Delete from table1  WHERE ID = " + id  ;
// Execute s1 here

Try this -

public void delete() throws SQLException {
    DataConnection connection = new DataConnection();
    String query1 = " DELETE FROM " + table2 + " WHERE ID = " + id;
    String query2 = " DELETE FROM " + Table1 + " WHERE ID = " + id;
    //  String query = query1 + " " + query2;
    connection.updateData(query1);
    connection.updateData(query2);
    connection.closeConnection();

edited Apr 26, 2014 at 17:01

answered Apr 26, 2014 at 16:34

Amit

2883 silver badges11 bronze badges

1 Comment

ChaseHardin Over a year ago

I tried this and was getting errors, so tried the following: public void delete() throws SQLException { DataConnection connection = new DataConnection(); String query1 = " DELETE FROM " + table2 + " WHERE ID = " + id; String query2 = " DELETE FROM " + Table1 + " WHERE ID = " + id; String query = query1 + " " + query2; connection.updateData(query); connection.closeConnection(); }

Collectives™ on Stack Overflow

Delete Rows in Two Tables with Java and SQL

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related