Update table SQL

Question

I have a table that contains two columns CODE where there is all the bar code and VARIABLE initialized to 0 at the beginning . I have to update the table when I scan the bar code , I have to add 1 to variable , when I scan it twice VARIABLE =2.... I have tried to do it like but it does not work .Can anyone help?

 String query = "INSERT INTO TABLE (CODE,VARIABLE) VALUES(?,?) ON DUPLICATE KEY UPDATE VARIABLE='"+VARIABLE+1+"'";

                try {
                    if (connect != null) {
                           PreparedStatement statement = connect.prepareStatement(query);
                         statement.setString(1, "%" + res + "%");
                        statement.setInt(2,VARIABLE );

                        r=statement.executeQuery();
                        if (r.next()) {
                            message = "Updated";
                            String code = r.getString("CODE");
                            int var = r.getInt("VARIABLE");
                            INFOSOMME.setText(message);
                            INFOSOMME.setText(code);
                            INFOSOMME.setText(var);
                        } else {
                            message = "Error";
                            INFOSOMME.setText(message);
                        }
                    } else {
                        message = "Error in connection with SQL server";
                        INFOSOMME.setText(message);

                    }

                } catch (SQLException e) {
                    etat = false;
                    message = "Got an exception!";
                    System.err.println(e.getMessage());
                }


            }
        });

ERROR: 08-03 09:43:44.966 30393-30393/com.example.practicas.myapplication W/System.err: Sintaxis incorrecta cerca de la palabra clave 'ON'.

I have tried to change the query to String query="INSERT INTO TABLE(CODE,VARIABLE) VALUES(?,0);" + "Update TABLE SET VARIABLE=VARIABLE+1 WHERE CODE LIKE ?"; and I got this error /System.err: The executeQuery method must return a result set.

Give [this answer](https://stackoverflow.com/a/2718352/469080) a go, I think this is what you need. Basically `INSERT OR IGNORE` your barcode, then `UPDATE` the value in a subsequent statement. — Michael Dodd, Aug 03 '17 at 08:54
why did not you use VARIABLE+1 as parameter as like VALUES(*?*,*?*) ? — Abdullah Tellioglu, Aug 03 '17 at 08:54
I have tried to change the query to String query="INSERT INTO TABLE(CODE,VARIABLE) VALUES(?,0);" + "Update TABLE SET VARIABLE=VARIABLE+1 WHERE CODE LIKE ?"; and I got this error /System.err: The executeQuery method must return a result set. — Mimi, Aug 03 '17 at 09:30
Please for your own good, update the last value to be parameterized, else you're wide open to SQL Injection attacks. Also as was suggested in one of the answers, you're not executing a query to return a result set, but rather a non query. — user2366842, Aug 03 '17 at 13:38

score 0 · Answer 1 · answered Aug 03 '17 at 08:56

0

Try to execute the following SQL command:

String query = "INSERT INTO TABLE (CODE,VARIABLE) VALUES(?,?) ON DUPLICATE KEY UPDATE VARIABLE=(VARIABLE+1)";

answered Aug 03 '17 at 08:56

cotnic

158
2
11

Thanks for your answer but I still get the same error – Mimi Aug 03 '17 at 08:59
try again with instead of "VARIABLE=(VARIABLE+1)" you enter "VARIABLE=(VALUES(VARIABLE)+1)" – cotnic Aug 03 '17 at 09:02

score 0 · Answer 2 · answered Aug 03 '17 at 11:42

0

I have posted a comment but better here, I guess:

executeNonQuery

Updates and Inserts don't return data, so you must not use executeQuery with them (which does return information apart from the amount of rows affected)

answered Aug 03 '17 at 11:42

D Ie

841
7
23

@Mimi did it work? If so, mark it as accepted answer, if not, let us know in order to help you further – D Ie Aug 07 '17 at 07:03
Thanks a lot for your answer . I changed the query String query = " Update TABLE SET VARIABLE=VARIABLE+1 WHERE CODE LIKE ?"; and it works. No more errors – Mimi Aug 07 '17 at 13:27

Update table SQL

2 Answers2