2

I need a single SQLite query to insert or update in SQLite table.

MyTable:

  1. Id(Primary key and not null)
  2. Name
  3. PhoneNumber

enter image description here

Expected Query: If the Id is not present then have to insert a new row and If the existing row value is changed then update the row when inserting multiple row.

EDIT 1:

I have posted INSERT query i have tried in Insert Multiple Rows in SQLite Error (error code = 1). Like that i I have tried using "INSERT OR REPLACE". But it is working with Firfox SQLite Manager Not working with Android SQLite.

I have used sDataBase.execSQL(query) to execute this query.

Community
  • 1
  • 1
Ponmalar
  • 6,871
  • 10
  • 50
  • 80
  • Just a note that may help someone. It **is** perfectly OK to have a batch command (so .. values (...) , (...) , (...) and so on) with "insert or replace". – Fattie Feb 28 '17 at 15:18
  • Just another note that may help someone :) interestingly performance tests show that "insert or replace" is very little slower (5% at worst) than plain "insert". – Fattie Feb 28 '17 at 15:23

2 Answers2

2

Try this:

String sql = "INSERT OR REPLACE INTO MyTable (Name, PhoneNumber) VALUES (?,?)";
SQLiteStatement st = db.compileStatement(sql);

And write or update:

    public void writeOrUpdateData(ArrayList<MyClass> data) {
    try {

        db.beginTransaction();

        for(int i = 0; i < data.size(); i++) {

            st.clearBindings();
            st.bindLong(1, data.get(i).getName());
            st.bindString(2, data.get(i).getPhoneNumber);
            st.executeInsert();

        }

        db.setTransactionSuccessful();

    } 
    catch(Exception e) {} 
    finally {
        db.endTransaction();
    }
}

This way you get bulk insert/update, which is quite efficient!

Roman
  • 2,079
  • 4
  • 35
  • 53
  • Multiple row will not be working with your query. I have tried. http://stackoverflow.com/questions/1609637/is-it-possible-to-insert-multiple-rows-at-a-time-in-an-sqlite-database – Ponmalar Mar 26 '13 at 16:12
  • @Ponmalar Emm, I expect that you have some custom class objects, which you want to write/update in local sqlite db. So bulk insert/update would work just fine. Pass multiple objects within ArrayList – Roman Mar 26 '13 at 16:14
  • Thanks for your response. i am sending query from webview. Inside WebView I will execute the query using sDataBase.execSQL(query); So I need a query only – Ponmalar Mar 26 '13 at 16:17
0

I have created database with primary key only in Firefox SQLite Manager. I have missed this in SQLite database in Android. Now i have created database with PRIMARY KEY and NOT NULL. Now Working fine.

Ponmalar
  • 6,871
  • 10
  • 50
  • 80