SQLite in Android How to update a specific row

Question

I've been trying to update a specific row for a while now, and it seems that there are two ways to do this. From what I've read and tried, you can just use the:

execSQL(String sql) method

or the:

update(String table, ContentValues values, String whereClause, String[] whereArgs) method.

(Let me know if this is incorrect as I am new to android and very new to SQL.)

So let me get to my actual code.

myDB.update(TableName, "(Field1, Field2, Field3)" + " VALUES ('Bob', 19, 'Male')", "where _id = 1", null);

I am trying to accomplish this:

Update Field1, Field2, and Field3 where the primary key (_id) is equal to 1.

Eclipse gives me a red line right underneath the word "update" and gives me this explanation:

The method update(String, ContentValues, String, String[]) in the type SQLiteDatabase is not applicable for the arguments (String, String, String, null)

I'm guessing I'm not assigning the ContentValues correctly. Can anyone point me in the right direction?

Akhil · Accepted Answer · 2020-11-28T22:01:03.143

323

First make a ContentValues object :

ContentValues cv = new ContentValues();
cv.put("Field1","Bob"); //These Fields should be your String values of actual column names
cv.put("Field2","19");
cv.put("Field2","Male");

Then use the update method, it should work now:

myDB.update(TableName, cv, "_id = ?", new String[]{id});

edited Nov 28 '20 at 22:01

answered Mar 21 '12 at 04:18

Akhil

13,888
7
35
39

Eclipse is giving me red underlines on "Field1", "Field2", and "Field3". Suggestions? – EGHDK Mar 21 '12 at 04:25
1

sorry, I thought they are variables declared in your code. Put them in double quotes. – Akhil Mar 21 '12 at 04:39
Thanks for being the first one with the correct answer. Saved me a lot of time. Much appreciated. – EGHDK Mar 21 '12 at 04:40
3

what if i want to check, if that row exists in table or not and then decide whether to update or insert a row? – Akash Raghav Jul 22 '16 at 07:35
12

It's actually a walk-around. The Third param of db.update() should be the where clause only, and the fourth is the actual condition values. In this case, the line should be: `myDB.update(TableName, cv, "_id=?", new String[]{id})`. SQLite will automatically fill the fourth param into the "?" in the third param, i.e. the WHERE clause. If your third param contains n "?"s , the fourth param should be a String[] of length n – CristianoYL Jul 13 '17 at 17:32
@Akhil Your method would not work in some circumstances, for example, if the value contains symbols. – CristianoYL Jul 13 '17 at 17:34
I'm having error with this, because the id column was declared as integer. How to fix this since new String[]{id} only accepts string? – lance2k Mar 22 '22 at 14:39

Yaqub Ahmad · Answer 2 · 2013-10-19T02:01:50.470

55

Simple way:

String strSQL = "UPDATE myTable SET Column1 = someValue WHERE columnId = "+ someValue;

myDataBase.execSQL(strSQL);

edited Oct 19 '13 at 02:01

answered Mar 21 '12 at 04:25

Yaqub Ahmad

27,569
23
102
149

7

This code will throw IllegalArgumentException. If you execute query without ContentValues it'd be much better to avoid using second argument at all. Like: myDataBase.execSQL(strSQL); – Igor V Savchenko Oct 18 '13 at 15:20
using `execSQL()` for updates won't work. Read [execSQL() with UPDATE doesn't update](https://stackoverflow.com/q/17026633/2408342). – Roshana Pitigala Jun 23 '19 at 18:02
There is a serious security risk in doing this because someone can pass an SQL statement to 'someValue' variable, which could alter the whole database. Therefore always try to do prepared statements when doing any database operations. – Achintha Isuru Dec 19 '19 at 15:16

score 44 · Answer 3 · edited Jul 06 '19 at 08:12

44

At first create a ContentValues object :

ContentValues cv = new ContentValues();
cv.put("Field1","Bob");
cv.put("Field2","19");

Then use the update method. Note, the third argument is the where clause. The "?" is a placeholder. It will be replaced with the fourth argument (id)

myDB.update(MY_TABLE_NAME, cv, "_id = ?", new String[]{id});

This is the cleanest solution to update a specific row.

edited Jul 06 '19 at 08:12

Christoph Grimmer

4,210
4
40
64

answered Jan 23 '15 at 16:47

funcoder

1,975
20
14

The best way since using ? is safe. – Akash Agarwal Apr 26 '16 at 11:17
2

And what does that ? do? – alphiii Feb 16 '17 at 11:22
1

The parameter approach with ? is the best and most safe way. This should be the accepted answer. Never use string concatenation when composing sql statements! – Grisgram Jul 02 '18 at 09:27
how to use when where clause contains two fields? – Banee Ishaque K Oct 24 '19 at 07:22

score 27 · Answer 4 · answered Mar 21 '12 at 04:32

I personally prefere .update for its convenience. But execsql will work same.
You are right with your guess that the problem is your content values. You should create a ContentValue Object and put the values for your database row there.

This code should fix your example:

 ContentValues data=new ContentValues();
 data.put("Field1","bob");
 data.put("Field2",19);
 data.put("Field3","male");
 DB.update(Tablename, data, "_id=" + id, null);

score 15 · Answer 5 · edited Dec 10 '15 at 12:38

15

you can try this...

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

edited Dec 10 '15 at 12:38

Werner Henze

16,404
12
44
69

answered Dec 10 '15 at 12:17

Murugan.P

335
3
13

score 7 · Answer 6 · edited Feb 08 '18 at 19:23

7

hope this'll help you:

public boolean updatedetails(long rowId, String address)
  {
     SQLiteDatabase mDb= this.getWritableDatabase();
   ContentValues args = new ContentValues();
   args.put(KEY_ROWID, rowId);          
   args.put(KEY_ADDRESS, address);
  return mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null)>0;   
 }

edited Feb 08 '18 at 19:23

Michael

588
11
19

answered Mar 21 '12 at 04:45

sachi

2,122
7
30
46

score 6 · Answer 7 · edited Mar 21 '12 at 05:13

6

You try this one update method in SQLite

int id;
ContentValues con = new ContentValues();
con.put(TITLE, title);
con.put(AREA, area);
con.put(DESCR, desc);
con.put(TAG, tag);
myDataBase.update(TABLE, con, KEY_ID + "=" + id,null);

edited Mar 21 '12 at 05:13

Alex Lockwood

83,063
39
206
250

answered Mar 21 '12 at 04:18

Android

1,417
9
11

score 5 · Answer 8 · edited Oct 06 '16 at 11:54

use this code in your DB `

public boolean updatedetails(long rowId,String name, String address)
      {
       ContentValues args = new ContentValues();
       args.put(KEY_ROWID, rowId);          
       args.put(KEY_NAME, name);
       args.put(KEY_ADDRESS, address);
       int i =  mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null);
    return i > 0;
     }

for updating in your sample.java use this code

  //DB.open();

        try{
              //capture the data from UI
              String name = ((EditText)findViewById(R.id.name)).getText().toString().trim();
              String address =(EditText)findViewById(R.id.address)).getText().toString().trim();

              //open Db
              pdb.open();

              //Save into DBS
              pdb.updatedetails(RowId, name, address);
              Toast.makeText(this, "Modified Successfully", Toast.LENGTH_SHORT).show();
              pdb.close();
              startActivity(new Intent(this, sample.class));
              finish();
        }catch (Exception e) {
            Log.e(TAG_AVV, "errorrrrr !!");
            e.printStackTrace();
        }
    pdb.close();

score 3 · Answer 9 · answered Aug 20 '15 at 11:25

3

Can try like this:

ContentValues values=new ContentValues();
values.put("name","aaa");
values.put("publisher","ppp");
values.put("price","111");

int id=sqdb.update("table_name",values,"bookid='5' and booktype='comic'",null);

answered Aug 20 '15 at 11:25

Amir

1,066
1
13
26

score 2 · Answer 10 · answered Jul 10 '14 at 19:49

For updates, need to call setTransactionSuccessfull for changes to get committed like so:

db.beginTransaction();
try {
    db.update(...) 
    db.setTransactionSuccessfull(); // changes get rolled back if this not called
} finally {
   db.endTransaction(); // commit or rollback
}

score 2 · Answer 11 · answered Feb 13 '15 at 06:56

//Here is some simple sample code for update

//First declare this

private DatabaseAppHelper dbhelper;
private SQLiteDatabase db;

//initialize the following

dbhelper=new DatabaseAppHelper(this);
        db=dbhelper.getWritableDatabase();

//updation code

 ContentValues values= new ContentValues();
                values.put(DatabaseAppHelper.KEY_PEDNAME, ped_name);
                values.put(DatabaseAppHelper.KEY_PEDPHONE, ped_phone);
                values.put(DatabaseAppHelper.KEY_PEDLOCATION, ped_location);
                values.put(DatabaseAppHelper.KEY_PEDEMAIL, ped_emailid);
                db.update(DatabaseAppHelper.TABLE_NAME, values,  DatabaseAppHelper.KEY_ID + "=" + ?, null);

//put ur id instead of the 'question mark' is a function in my shared preference.

score 2 · Answer 12 · answered Dec 05 '16 at 10:05

 public void updateRecord(ContactModel contact) {
    database = this.getReadableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(COLUMN_FIRST_NAME, contact.getFirstName());
    contentValues.put(COLUMN_LAST_NAME, contact.getLastName());
    contentValues.put(COLUMN_NUMBER,contact.getNumber());
    contentValues.put(COLUMN_BALANCE,contact.getBalance());
    database.update(TABLE_NAME, contentValues, COLUMN_ID + " = ?", new String[]{contact.getID()});
    database.close();
}

score 2 · Answer 13 · edited Mar 21 '12 at 04:13

2

if your sqlite row has a unique id or other equivatent, you can use where clause, like this

update .... where id = {here is your unique row id}

edited Mar 21 '12 at 04:13

Lucifer

29,392
25
90
143

answered Mar 21 '12 at 04:12

mcxiaoke

188
2
8

Still getting the same error as stated in my question. I did change the third parameter (String whereClause) to `"where _id = 1"`. The change is also reflected in my question as well. – EGHDK Mar 21 '12 at 04:19

score 1 · Answer 14 · edited May 26 '13 at 15:57

1

just try this way

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

edited May 26 '13 at 15:57

Serkan Arıkuşu

5,549
5
33
50

answered Jun 28 '12 at 13:37

Satyam

1,672
3
20
34

score 1 · Answer 15 · answered Mar 14 '18 at 18:16

1

Method for updation in SQLite:

public void updateMethod(String name, String updatename){
    String query="update students set email = ? where name = ?";
    String[] selections={updatename, name};
    Cursor cursor=db.rawQuery(query, selections);
}

answered Mar 14 '18 at 18:16

Muhammad Raza Saeed

31
1

AllanRibas · Answer 16 · 2020-06-10T19:11:02.540

I will demonstrate with a complete example

Create your database this way

    import android.content.Context
    import android.database.sqlite.SQLiteDatabase
    import android.database.sqlite.SQLiteOpenHelper

    class DBHelper(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {
        override fun onCreate(db: SQLiteDatabase) {
            val createProductsTable = ("CREATE TABLE " + Business.TABLE + "("
                    + Business.idKey + " INTEGER PRIMARY KEY AUTOINCREMENT ,"
                    + Business.KEY_a + " TEXT, "
                    + Business.KEY_b + " TEXT, "
                    + Business.KEY_c + " TEXT, "
                    + Business.KEY_d + " TEXT, "
                    + Business.KEY_e + " TEXT )")
            db.execSQL(createProductsTable)
        }
        override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
            // Drop older table if existed, all data will be gone!!!
            db.execSQL("DROP TABLE IF EXISTS " + Business.TABLE)
            // Create tables again
            onCreate(db)

        }
        companion object {
            //version number to upgrade database version
            //each time if you Add, Edit table, you need to change the
            //version number.
            private val DATABASE_VERSION = 1

            // Database Name
            private val DATABASE_NAME = "business.db"
        }
    }

Then create a class to facilitate CRUD -> Create|Read|Update|Delete

class Business {
    var a: String? = null
    var b: String? = null
    var c: String? = null
    var d: String? = null
    var e: String? = null

    companion object {
        // Labels table name
        const val TABLE = "Business"
        // Labels Table Columns names
        const val rowIdKey = "_id"
        const val idKey = "id"
        const val KEY_a = "a"
        const val KEY_b = "b"
        const val KEY_c = "c"
        const val KEY_d = "d"
        const val KEY_e = "e"
    }
}

Now comes the magic

import android.content.ContentValues
import android.content.Context

    class SQLiteDatabaseCrud(context: Context) {
        private val dbHelper: DBHelper = DBHelper(context)

        fun updateCart(id: Int, mBusiness: Business) {
            val db = dbHelper.writableDatabase
            val valueToChange = mBusiness.e
            val values = ContentValues().apply {
                put(Business.KEY_e, valueToChange)
            }
            db.update(Business.TABLE, values, "id=$id", null)
            db.close() // Closing database connection
        }
    }

you must create your ProductsAdapter which must return a CursorAdapter

So in an activity just call the function like this

internal var cursor: Cursor? = null
internal lateinit var mProductsAdapter: ProductsAdapter

 mSQLiteDatabaseCrud = SQLiteDatabaseCrud(this)
    try {
        val mBusiness = Business()
        mProductsAdapter = ProductsAdapter(this, c = todoCursor, flags = 0)
        lstProducts.adapter = mProductsAdapter


        lstProducts.onItemClickListener = OnItemClickListener { parent, view, position, arg3 ->
                val cur = mProductsAdapter.getItem(position) as Cursor
                cur.moveToPosition(position)
                val id = cur.getInt(cur.getColumnIndexOrThrow(Business.idKey))

                mBusiness.e = "this will replace the 0 in a specific position"
                mSQLiteDatabaseCrud?.updateCart(id ,mBusiness)

            }

        cursor = dataBaseMCRUD!!.productsList
        mProductsAdapter.swapCursor(cursor)
    } catch (e: Exception) {
        Log.d("ExceptionAdapter :",""+e)
    }

score 1 · Answer 17 · answered Jun 16 '20 at 13:50

SQLiteDatabase myDB = this.getWritableDatabase();

ContentValues cv = new ContentValues();
cv.put(key1,value1);    
cv.put(key2,value2); /*All values are your updated values, here you are 
                       putting these values in a ContentValues object */
..................
..................

int val=myDB.update(TableName, cv, key_name +"=?", new String[]{value});

if(val>0)
 //Successfully Updated
else
 //Updation failed

score 1 · Answer 18 · answered May 02 '21 at 13:21

Here I have completed this kind of code for update the row of a database, this is the code of Database handler class

public Boolean updateData(String id,String name,String age,String gender){
    SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(ID,id);
    contentValues.put(NAME,name);
    contentValues.put(AGE,age);
    contentValues.put(GENDER,gender);

    sqLiteDatabase.update(TABLE_NAME,contentValues,ID+"= ?",new String[]{id});
    return true;           //Boolean value return korbe
}

João Rosa · Answer 19 · 2013-06-19T22:08:39.970

0

public long fillDataTempo(String table){
    String[] table = new String[1];
    tabela[0] = table; 
    ContentValues args = new ContentValues();
    args.put(DBOpenHelper.DATA_HORA, new Date().toString());
    args.put(DBOpenHelper.NOME_TABELA, nome_tabela);
    return db.update(DATABASE_TABLE, args, STRING + " LIKE ?" ,tabela);
}

edited Jun 19 '13 at 22:08

answered Jun 19 '13 at 21:18

João Rosa

1
1

score 0 · Answer 20 · answered Feb 17 '20 at 12:08

just give rowId and type of data that is going to be update in ContentValues.

public void updateStatus(String id , int status){

SQLiteDatabase db = this.getWritableDatabase();

ContentValues data = new ContentValues();

data.put("status", status);

db.update(TableName, data, "columnName" + " = "+id , null);

}

SQLite in Android How to update a specific row

20 Answers20

Linked

Related