0

so i was trying to run these lines of code


    public void refresh() {
        try {
            String strUpdate = "SET SQL_SAFE_UPDATES = 0; \n"
                    + "delete from cart where cart.ID in (select * from (select cart.ID from cart where cart.ID not in (select cart.ID from cart inner join orders where cart.OrderID = orders.OrderID))tblTmp);\n"
                    + "SET SQL_SAFE_UPDATES = 1;";
            System.out.println(strUpdate);
            pstm = cnn.prepareStatement(strUpdate);
            pstm.executeUpdate();
        } catch (Exception e) {
            System.out.println("RefreshCart:" + e.getMessage());
        }
    }

The 3 sql line ran fine in mySQL enter image description here but when i try to ran it in this function it returns an error enter image description here

i have the SQL statement printed out using System.out.println and it returned fine, even works when i recopy it back into mySQL workbench enter image description here

BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
Quang Pham
  • 11
  • 1
  • 1
    I'm not sure you can include multiple statements into one execution. Also, you should remove those `\n`s. At best they're useless, at worst they break the execution. – Federico klez Culloca May 30 '23 at 13:18
  • What is the purpose of `SET SQL_SAFE_UPDATES = 0`? – Salman A May 30 '23 at 13:24
  • SET SQL_SAFE_UPDATES = 0; because mySQL is on save mode by default and i can't delete the data without it – Quang Pham May 30 '23 at 13:26
  • @FedericoklezCulloca i tried to remove \n but the error is still the same, i don't know what to do now – Quang Pham May 30 '23 at 13:27
  • @SManikandan i just tried replacing "*" with ID since there is only one column in the table, it still returns "RefreshCart: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 'delete from cart where cart.ID in (select ID from (select cart.ID from cart wher' at line 1" – Quang Pham May 30 '23 at 13:32
  • replace * with "tblTmp.id " – S Manikandan May 30 '23 at 13:39

1 Answers1

0

OK, so what did work for me is to divide the sql statement into 3 parts, execute them in seperate functions and then make a function that does 3 of them step by step like so

    public void deleteData() {
    try {
        String strUpdate = "delete from cart where cart.ID in (select ID from (select cart.ID from cart where cart.ID not in (select cart.ID from cart inner join orders where cart.OrderID = orders.OrderID))tblTmp)";
        System.out.println(strUpdate);
        pstm = cnn.prepareStatement(strUpdate);
        pstm.executeUpdate();
    } catch (Exception e) {
        System.out.println("RefreshCart:" + e.getMessage());
    }
}

public void sqlUnsafe() {
    try {
        String strUpdate = "SET SQL_SAFE_UPDATES = 0";
        System.out.println(strUpdate);
        pstm = cnn.prepareStatement(strUpdate);
        pstm.executeUpdate();
    } catch (Exception e) {
        System.out.println("RefreshCart:" + e.getMessage());
    }
}
 public void sqlSafe() {
    try {
        String strUpdate = "SET SQL_SAFE_UPDATES = 1";
        System.out.println(strUpdate);
        pstm = cnn.prepareStatement(strUpdate);
        pstm.executeUpdate();
    } catch (Exception e) {
        System.out.println("RefreshCart:" + e.getMessage());
    }
}
 
public void refresh(){
    this.sqlUnsafe();
    this.deleteData();
    this.sqlSafe();
}
Quang Pham
  • 11
  • 1