Android SQLite: Update Statement

Question

I need to implement SQLite in my application. I followed this tutorial: Creating and using databases in Android one

Everything is working fine. I inserted 1 row with 5 columns. Now I want to update the value of 1 column only and the others will remain the same.

There is one update method in the tutorial that needs all the parameters, however, I want to update only one column.

The requested URL /index.php/articlesdatastorage/235-creating-and-using-databases-in-android-one was not found on this server. — Machado
– Machado, Commented Mar 11, 2015 at 18:28

TheHippo · Accepted Answer · 2013-12-17 14:26:34Z

165

You can use the code below.

String strFilter = "_id=" + Id;
ContentValues args = new ContentValues();
args.put(KEY_TITLE, title);
myDB.update("titles", args, strFilter, null);

edited Dec 17, 2013 at 14:26

TheHippo

63.3k15 gold badges77 silver badges101 bronze badges

answered May 13, 2011 at 6:16

Nikhil

16.2k20 gold badges66 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

joshhendo Over a year ago

You could even do: myDB.update("titles", args, strFilter, new String[] { Integer.toString(ID) }); if you have strFilter set to "_id=?"... The annoying thing with a single item is that you still need to put it in an array, which can make it inconvenient. Still, up voted for the right answer.

Simonw Over a year ago

@Dheeraj - This will only update the column which is added to the ContentValues object

r41n Over a year ago

If I'm not wrong, @joshhendo 's code should also prevent SQL injections, something this answer doesn't consider. Since the argument is passed as a parameter it is escaped by the underlying code.

Guillaume Over a year ago

You would need to put quotes around your Id. Something like: String strFilter = "_id='" + Id + "'";

luiscarlostic · Accepted Answer · 2014-04-09 14:34:36Z

119

You can try:

db.execSQL("UPDATE DB_TABLE SET YOUR_COLUMN='newValue' WHERE id=6 ");

Or

ContentValues newValues = new ContentValues();
newValues.put("YOUR_COLUMN", "newValue");

db.update("YOUR_TABLE", newValues, "id=6", null);

Or

ContentValues newValues = new ContentValues();
newValues.put("YOUR_COLUMN", "newValue");

String[] args = new String[]{"user1", "user2"};
db.update("YOUR_TABLE", newValues, "name=? OR name=?", args);

edited Apr 9, 2014 at 14:34

answered Aug 22, 2013 at 21:07

luiscarlostic

1,2461 gold badge8 silver badges7 bronze badges

Comments

Stephan · Accepted Answer · 2011-05-13 06:15:00Z

53

It's all in the tutorial how to do that:

    ContentValues args = new ContentValues();
    args.put(columnName, newValue);
    db.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null);

Use ContentValues to set the updated columns and than the update() method in which you have to specifiy, the table and a criteria to only update the rows you want to update.

answered May 13, 2011 at 6:15

Stephan

7,38839 silver badges46 bronze badges

2 Comments

Dheeraj Vepakomma Over a year ago

-1. The question is to update a single column in a row, not to update a whole row. Alex's answer is correct.

Simon Rolin Over a year ago

Dheeraj, you can just add as many args.put(columnName, newValue) as you want.

Alex Gitelman · Accepted Answer · 2011-05-13 06:11:26Z

18

you can always execute SQL.

update [your table] set [your column]=value

for example

update Foo set Bar=125

answered May 13, 2011 at 6:11

Alex Gitelman

24.8k7 gold badges54 silver badges50 bronze badges

8 Comments

Stephan Over a year ago

Why using an explicit SQL statement, if there is an (pretty easy and convenient) API for that?

Rafael Nobre Over a year ago

@Stephan : Because its not standard SQLite. The API ties up too much specially when the DB is shared between apps on different platforms

Dheeraj Vepakomma Over a year ago

@Stephan This is the only correct answer to the question. The OP wants to update a single column in a row, not to update a whole row.

Daniel Over a year ago

Using the API is a better bet here, I'm surprised there isn't a middle solution, the C library lets you define an SQL to execute and set formatters and tokens, the next step is using the C api to bind variables to the tokens in the SQL string, thus avoiding parsing errors and quote problems etc... here it's almost too assisted, not even writing even part of the SQL, doesn't feel nice... but rather this then a concatenated SQL string.

Sunny Over a year ago

This method will fail when string contain single quotes and some special character. @Nike answer is correct.

|

Adnan Bin Mustafa · Accepted Answer · 2017-04-01 08:14:11Z

I use this class to handle database.I hope it will help some one in future.

Happy coding.

public class Database {

private static class DBHelper extends SQLiteOpenHelper {

    /**
     * Database name
     */
    private static final String DB_NAME = "db_name";

    /**
     * Table Names
     */
    public static final String TABLE_CART = "DB_CART";


    /**
     *  Cart Table Columns
     */
    public static final String CART_ID_PK = "_id";// Primary key

    public static final String CART_DISH_NAME = "dish_name";
    public static final String CART_DISH_ID = "menu_item_id";
    public static final String CART_DISH_QTY = "dish_qty";
    public static final String CART_DISH_PRICE = "dish_price";

    /**
     * String to create reservation tabs table
     */
    private final String CREATE_TABLE_CART = "CREATE TABLE IF NOT EXISTS "
            + TABLE_CART + " ( "
            + CART_ID_PK + " INTEGER PRIMARY KEY, "
            + CART_DISH_NAME + " TEXT , "
            + CART_DISH_ID + " TEXT , "
            + CART_DISH_QTY + " TEXT , "
            + CART_DISH_PRICE + " TEXT);";


    public DBHelper(Context context) {
        super(context, DB_NAME, null, 2);

    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_TABLE_CART);


    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
        db.execSQL("DROP TABLE IF EXISTS " + CREATE_TABLE_CART);
        onCreate(db);
    }

}


     /**
      * CART handler
      */
      public static class Cart {


    /**
     * Check if Cart is available or not
     *
     * @param context
     * @return
     */
    public static boolean isCartAvailable(Context context) {

        DBHelper dbHelper = new DBHelper(context);
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        boolean exists = false;

        try {
            String query = "SELECT * FROM " + DBHelper.TABLE_CART;
            Cursor cursor = db.rawQuery(query, null);
            exists = (cursor.getCount() > 0);
            cursor.close();
            db.close();
        } catch (SQLiteException e) {
            db.close();
        }

        return exists;
    }


    /**
     * Insert values in cart table
     *
     * @param context
     * @param dishName
     * @param dishPrice
     * @param dishQty
     * @return
     */
    public static boolean insertItem(Context context, String itemId, String dishName, String dishPrice, String dishQty) {

        DBHelper dbHelper = new DBHelper(context);
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(DBHelper.CART_DISH_ID, "" + itemId);
        values.put(DBHelper.CART_DISH_NAME, "" + dishName);
        values.put(DBHelper.CART_DISH_PRICE, "" + dishPrice);
        values.put(DBHelper.CART_DISH_QTY, "" + dishQty);

        try {
            db.insert(DBHelper.TABLE_CART, null, values);
            db.close();
            return true;
        } catch (SQLiteException e) {
            db.close();
            return false;
        }
    }

    /**
     * Check for specific record by name
     *
     * @param context
     * @param dishName
     * @return
     */
    public static boolean isItemAvailable(Context context, String dishName) {

        DBHelper dbHelper = new DBHelper(context);
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        boolean exists = false;

        String query = "SELECT * FROM " + DBHelper.TABLE_CART + " WHERE "
                + DBHelper.CART_DISH_NAME + " = '" + String.valueOf(dishName) + "'";


        try {
            Cursor cursor = db.rawQuery(query, null);

            exists = (cursor.getCount() > 0);
            cursor.close();

        } catch (SQLiteException e) {

            e.printStackTrace();
            db.close();

        }

        return exists;
    }

    /**
     * Update cart item by item name
     *
     * @param context
     * @param dishName
     * @param dishPrice
     * @param dishQty
     * @return
     */
    public static boolean updateItem(Context context, String itemId, String dishName, String dishPrice, String dishQty) {

        DBHelper dbHelper = new DBHelper(context);
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(DBHelper.CART_DISH_ID, itemId);
        values.put(DBHelper.CART_DISH_NAME, dishName);
        values.put(DBHelper.CART_DISH_PRICE, dishPrice);
        values.put(DBHelper.CART_DISH_QTY, dishQty);

        try {

            String[] args = new String[]{dishName};
            db.update(DBHelper.TABLE_CART, values, DBHelper.CART_DISH_NAME + "=?", args);

            db.close();


            return true;
        } catch (SQLiteException e) {
            db.close();

            return false;
        }
    }

    /**
     * Get cart list
     *
     * @param context
     * @return
     */
    public static ArrayList<CartModel> getCartList(Context context) {

        DBHelper dbHelper = new DBHelper(context);
        SQLiteDatabase db = dbHelper.getReadableDatabase();

        ArrayList<CartModel> cartList = new ArrayList<>();

        try {
            String query = "SELECT * FROM " + DBHelper.TABLE_CART + ";";

            Cursor cursor = db.rawQuery(query, null);


            for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {

                cartList.add(new CartModel(
                        cursor.getString(cursor.getColumnIndex(DBHelper.CART_DISH_ID)),
                        cursor.getString(cursor.getColumnIndex(DBHelper.CART_DISH_NAME)),
                        cursor.getString(cursor.getColumnIndex(DBHelper.CART_DISH_QTY)),
                        Integer.parseInt(cursor.getString(cursor.getColumnIndex(DBHelper.CART_DISH_PRICE)))
                ));
            }

            db.close();

        } catch (SQLiteException e) {
            db.close();
        }
        return cartList;
    }

   /**
     * Get total amount of cart items
     *
     * @param context
     * @return
     */
    public static String getTotalAmount(Context context) {

        DBHelper dbHelper = new DBHelper(context);
        SQLiteDatabase db = dbHelper.getReadableDatabase();

        double totalAmount = 0.0;

        try {
            String query = "SELECT * FROM " + DBHelper.TABLE_CART + ";";

            Cursor cursor = db.rawQuery(query, null);


            for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {

                totalAmount = totalAmount + Double.parseDouble(cursor.getString(cursor.getColumnIndex(DBHelper.CART_DISH_PRICE))) *
                        Double.parseDouble(cursor.getString(cursor.getColumnIndex(DBHelper.CART_DISH_QTY)));
            }

            db.close();


        } catch (SQLiteException e) {
            db.close();
        }


        if (totalAmount == 0.0)
            return "";
        else
            return "" + totalAmount;
    }


    /**
     * Get item quantity
     *
     * @param context
     * @param dishName
     * @return
     */
    public static String getItemQty(Context context, String dishName) {

        DBHelper dbHelper = new DBHelper(context);
        SQLiteDatabase db = dbHelper.getReadableDatabase();

        Cursor cursor = null;
        String query = "SELECT * FROM " + DBHelper.TABLE_CART + " WHERE " + DBHelper.CART_DISH_NAME + " = '" + dishName + "';";
        String quantity = "0";

        try {
            cursor = db.rawQuery(query, null);

            if (cursor.getCount() > 0) {

                cursor.moveToFirst();
                quantity = cursor.getString(cursor
                        .getColumnIndex(DBHelper.CART_DISH_QTY));

                return quantity;
            }


        } catch (SQLiteException e) {
            e.printStackTrace();
        }

        return quantity;
    }


    /**
     * Delete cart item by name
     *
     * @param context
     * @param dishName
     */
    public static void deleteCartItem(Context context, String dishName) {
        DBHelper dbHelper = new DBHelper(context);
        SQLiteDatabase db = dbHelper.getReadableDatabase();

        try {

            String[] args = new String[]{dishName};
            db.delete(DBHelper.TABLE_CART, DBHelper.CART_DISH_NAME + "=?", args);

            db.close();
        } catch (SQLiteException e) {
            db.close();
            e.printStackTrace();
        }

    }


}//End of cart class

/**
 * Delete database table
 *
 * @param context
 */
public static void deleteCart(Context context) {
    DBHelper dbHelper = new DBHelper(context);
    SQLiteDatabase db = dbHelper.getReadableDatabase();

    try {

        db.execSQL("DELETE FROM " + DBHelper.TABLE_CART);

    } catch (SQLiteException e) {
        e.printStackTrace();
    }

}

}

Usage:

  if(Database.Cart.isCartAvailable(context)){

       Database.deleteCart(context);

   }

TheHungryCub · Accepted Answer · 2016-09-21 06:51:13Z

1

The SQLiteDatabase object depends on the type of operation on the database.

More information, visit the official website:

https://developer.android.com/training/basics/data-storage/databases.html#UpdateDbRow

It explains how to manipulate consultations on the SQLite database.

INSERT ROW

Gets the data repository in write mode

SQLiteDatabase db = mDbHelper.getWritableDatabase();

Create a new map of values, where column names are the keys

ContentValues values = new ContentValues();
values.put(FeedEntry.COLUMN_NAME_ENTRY_ID, id);
values.put(FeedEntry.COLUMN_NAME_TITLE, title);
values.put(FeedEntry.COLUMN_NAME_CONTENT, content);

Insert the new row, returning the primary key value of the new row

long newRowId;
newRowId = db.insert(
     FeedEntry.TABLE_NAME,
     FeedEntry.COLUMN_NAME_NULLABLE,
     values);

UPDATE ROW

Define 'where' part of query.

String selection = FeedEntry.COLUMN_NAME_ENTRY_ID + " LIKE ?";

Specify arguments in placeholder order.

String[] selectionArgs = { String.valueOf(rowId) };


SQLiteDatabase db = mDbHelper.getReadableDatabase();

New value for one column

ContentValues values = new ContentValues();
values.put(FeedEntry.COLUMN_NAME_TITLE, title);

Which row to update, based on the ID

String selection = FeedEntry.COLUMN_NAME_ENTRY_ID + " LIKE ?";
String[] selectionArgs = { String.valueOf(rowId) };
    int count = db.update(
    FeedReaderDbHelper.FeedEntry.TABLE_NAME,
    values,
    selection,
    selectionArgs);

edited Sep 21, 2016 at 6:51

TheHungryCub

1,9452 gold badges14 silver badges26 bronze badges

answered Aug 6, 2016 at 21:40

Gustavo Eduardo Belduma

3,6792 gold badges12 silver badges7 bronze badges

2 Comments

Mike Over a year ago

Although the linked website may contain an answer to the question, your answer seems incomplete. Links can expire or break in another way. Please add relevant code/information from the linked website to your answer.

Gustavo Eduardo Belduma Over a year ago

Of course I understand, I just literally put the solution that worked for me and of course for its alleged source where I obtained

Collectives™ on Stack Overflow

Android SQLite: Update Statement

6 Answers 6

4 Comments

Comments

2 Comments

8 Comments

Comments

INSERT ROW

UPDATE ROW

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

4 Comments

Comments

2 Comments

8 Comments

Comments

INSERT ROW

UPDATE ROW

2 Comments

Linked

Related