How to update two tables in one query with SQLite [closed]

This is my first solution to my problem but it gives me a little delayed of the program while the data is updating. Is their more efficient way to make this work or alternative solution to my problem?

String updateProduct = "UPDATE prod_list SET prod_code = ?, item_name = ?, brand_name = ?, category_id = ? WHERE prod_code = ?";
                                        preparedStatement = connection.prepareStatement(updateProduct);
                                        preparedStatement.setString(1, textField1.getText());
                                        preparedStatement.setString(2, textField2.getText());
                                        preparedStatement.setString(3, textField3.getText());
                                        preparedStatement.setString(4, resultSet.getString("id"));
                                        preparedStatement.setString(5, controller.getProdCode());
                                        preparedStatement.executeUpdate();

                                        String updateInventory = "UPDATE inventory SET prod_code_id = ?, price = ?, updated_date = ? WHERE prod_code_id = ?";
                                        preparedStatement = connection.prepareStatement(updateInventory);
                                        preparedStatement.setString(1, textField1.getText());
                                        preparedStatement.setString(2, textField4.getText());
                                        preparedStatement.setString(3, formatter.format(now));
                                        preparedStatement.setString(4, controller.getProdCode());
                                        preparedStatement.executeUpdate();

Answer

One option is that you can use a Stored Procedure to execute your two separate queries and then pass your variables in (since some of them are already reused). The Stored Procedure is saved on the SQL server and you would just call it from within Java. Just noticed that you are using SQLite which doesn’t have SP IIRC.

You can try adding an index to the tables (prod_code and prod_code_id) you are trying to update.

CREATE INDEX IX_ProdList_ProdCode ON prod_list (prod_code);
CREATE INDEX IX_Inventiry_ProdCodeId ON inventory(prod_code_id );

If the UPDATE call is a one off thing and your program execution doesn’t depend on the result in the database being updated, you can fire it off asynchronously/on another thread so it doesn’t block and cause a delay and you can continue your program.