Insert or Replace Multiple Row with where condition in SQLite Query

Question

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

MyTable:

Id(Primary key and not null)
Name
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.

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
– Fattie, Commented Feb 28, 2017 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
– Fattie, Commented Feb 28, 2017 at 15:23

Roman · Accepted Answer · 2013-03-26 16:10:35Z

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!

answered Mar 26, 2013 at 16:10

Roman

2,0794 gold badges35 silver badges53 bronze badges

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

3 Comments

Ponmalar Over a year ago

Multiple row will not be working with your query. I have tried. stackoverflow.com/questions/1609637/…

Roman Over a year ago

@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

Ponmalar Over a year ago

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 · Accepted Answer · 2013-03-27 11:35:23Z

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.

answered Mar 27, 2013 at 11:35

Ponmalar

7,04110 gold badges53 silver badges80 bronze badges

Collectives™ on Stack Overflow

Insert or Replace Multiple Row with where condition in SQLite Query

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related