Help
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Ground Controller Lvl 1
Message 1 of 3

How to overwrite or skip same data in SQLite ?

Hello I have a project that location updates save in a SQLite database, but when new location comes from update if there is same data in the table I want to skip or overwrite it. This is my code.

public void DBCreate(){
        SQLITEDATABASE = openOrCreateDatabase("LatLongDatabase3", Context.MODE_PRIVATE, null);
        SQLITEDATABASE.execSQL("CREATE TABLE IF NOT EXISTS myTable72(id INTEGER PRIMARY KEY AUTOINCREMENT, FAVCurrentLocationLAT VARCHAR,FAVCurrentLocationLONG VARCHAR);");
    }
    public void SubmitData2SQLiteDB(){

            SQLiteQuery = "INSERT OR REPLACE INTO myTable72(id, FAVCurrentLocationLAT, FAVCurrentLocationLONG) VALUES(NULL,'"+mCurrentLocation.getLatitude()+"','"+mCurrentLocation.getLongitude()+"');";
            SQLITEDATABASE.execSQL(SQLiteQuery);

            Toast.makeText(CampaignActivity.this,"OK", Toast.LENGTH_LONG).show();
    }
2 Replies
Community Manager
Message 2 of 3

Re: How to overwrite or skip same data in SQLite ?

This post was moved to a different board that fits your topic of discussion a bit better. This means you’ll get better engagement on your post, and it keeps our community organized so users can more easily find information.

As you’ll notice, your topic is now here in the Project Development Help and Advice board. No action is needed on your part; you can continue the conversation as normal here.

Commander Lvl 1
Message 3 of 3

Re: How to overwrite or skip same data in SQLite ?

You might try something like this:

 

INSERT OR REPLACE INTO myTable72(id, FAVCurrentLocationLAT, FAVCurrentLocationLONG) VALUES 
( (SELECT id FROM myTable72 WHERE FAVCurrentLocationLAT='" +mCurrentLocation.getLatitude()+ "' 
    AND FAVCurrentLocationLONG='"+mCurrentLocation.getLongitude()+"'), 
'"+mCurrentLocation.getLatitude()+"','"+mCurrentLocation.getLongitude()+"');

 

I've not actually tested, nor can I easily confirm the parentheses are matched properly.

That aside, the gist is that if a row with the same lat/long values already exists, it will be

updated with the same values. If a row with the lat/long values doesn't already exist, the

'select id' sub-clause will return NULL and a new row will be inserted.

 

(Based on this question in StackOverflow).

 

Please follow-up to let us know how you made out. For good karma, mark a reply as the answer if it helped!