Query to delete from multiple tables in Access

Question

I want to delete one row from multiple tables in an Access database.

This is the code I tried to use in my project, but I got an error.

PreparedStatement ps = con.prepareStatement("DELETE FROM 'customer_details' , 'papers', 'magzines' WHERE 'customer_id' = ? ");   
ps.setString(1,tx1.getText());
int string = ps.executeUpdate();

Can anyone help me solve this?

Answer 1

I just tried the following and it worked for me:

String sql = 
        "DELETE t1.*, t2.*, t3.* " +
        "FROM " +
            "(" +
                "Table1 AS t1 " +
                "INNER JOIN " +
                "Table2 AS t2 " +
                    "ON t2.ID=t1.ID " +
            ")" +
            "INNER JOIN " +
            "Table3 AS t3 " +
                "ON t3.ID=t2.ID " +
        "WHERE t1.ID=?";
ps = con.prepareStatement(sql);
ps.setInt(1, 4);  // delete where ID=4
int n = ps.executeUpdate();

So in your case try something like this:

PreparedStatement ps = con.prepareStatement(
        "DELETE c.*, p.*, m.* " +
        "FROM " +
            "(" +
                "customer_details AS c " +
                "INNER JOIN " +
                "papers AS p " +
                    "ON p.customer_id=c.customer_id " +
            ")" +
            "INNER JOIN " +
            "magzines AS m " +
                "ON m.customer_id=p.customer_id " +
        "WHERE c.customer_id=?");   
ps.setString(1,tx1.getText());
int n = ps.executeUpdate();