SQLite - Can not update a row that has a long text for id column

Issue

So i am working on this Inventory Management app on Android Studio and having a trouble adding a certain amount of product into the database. I have a method in my database class called updateQuantity. It is taking a barcode number and an amount to add, and it's supposed to update the products quantity.

private static final String TABLE_PRODUCT = "product";

private static final String COLUMN_PRODUCT_BARCODE_NUMBER = "barcode_number"; // TEXT
private static final String COLUMN_PRODUCT_QUANTITY = "quantity"; // INTEGER
private static final String COLUMN_PRODUCT_NAME = "name"; // TEXT
private static final String COLUMN_PRODUCT_PURCHASE_PRICE = "purchase_price"; //INTEGER
private static final String COLUMN_PRODUCT_SALE_PRICE = "sale_price"; // INTEGER

public boolean updateQuantity(String barcode, int amount) {
    SQLiteDatabase db = this.getWritableDatabase();

    String query = "UPDATE " + TABLE_PRODUCT + " SET " + COLUMN_PRODUCT_QUANTITY +
                   " = ((SELECT " + COLUMN_PRODUCT_QUANTITY + " FROM " +
                   TABLE_PRODUCT + " WHERE " + COLUMN_PRODUCT_BARCODE_NUMBER +
                   " = " + barcode + ") + " + amount + ") WHERE " +
                    COLUMN_PRODUCT_BARCODE_NUMBER + " = " + barcode;
    try {
        db.execSQL(query);
    } catch (Exception e) {
        return false;
    }
    return true;
}`

It works with a product that has a barcode number "86938693" but does not do any quantity update for a product that has a barcode number "8690632034614". What is the problem here?

Solution

You created a very complicated sql statement, but it should be much simpler:

String query = 
    "UPDATE " + TABLE_PRODUCT + 
    " SET " + COLUMN_PRODUCT_QUANTITY + " = " + COLUMN_PRODUCT_QUANTITY + " + " + amount + 
    " WHERE " +  COLUMN_PRODUCT_BARCODE_NUMBER + " = '" + barcode + "'";

You don't have to search twice the table to get the value that you want to update.
Also the value of the variable barcode must be enclose inside single quotes as it is a string.
If there is a case that the value of the column COLUMN_PRODUCT_QUANTITY is NULL, then you must use coalesce():

String query = 
    "UPDATE " + TABLE_PRODUCT + 
    " SET " + COLUMN_PRODUCT_QUANTITY + " = coalesce(" + COLUMN_PRODUCT_QUANTITY + ", 0) + " + amount + 
    " WHERE " +  COLUMN_PRODUCT_BARCODE_NUMBER + " = '" + barcode + "'";

Answered By - forpas