SQLite Update and Increment with WHERE clause

Question

I am using SQLite and i am using a where clause to update a row in the database only if it exists and if it does not it needs to insert a new row

    public long createEntry(int x, int y, String date, int Version_Zero_HoursTable_One_DateTable)
{
    /*
     * first grab the values needed to increment the database values
     * */
    String[] column = new String[]{DATE,NUM_X,NUM_Y};
    Cursor c = ourDatabase.query(HOURS_TABLE, column, date, null, null,
            null, null);    
    int current_x =0;
    int current_y  = 0;
    int iX = c.getColumnIndex(NUM_X);
    int iY = c.getColumnIndex(NUM_Y);
    for(c.moveToFirst(); !c.isAfterLast(); c.moveToNext()){
        current_Y += c.getInt(iY);
        current_X += c.getInt(iX);
    }
    ContentValues cv = new ContentValues();
    cv.put(NUM_X, x + current_X);
    cv.put(NUM_Y, y + current_Y);
    cv.put(DATE, date);
    String WHEREargs = DATE+"="+date;
    if(Version_Zero_HoursTable_One_DateTable == 0)
    {
    return ourDatabase.update(HOURS_TABLE, cv, WHEREargs, null);
    //return ourDatabase.insert(HOURS_TABLE, null, cv);
    }
    else
    {
        return ourDatabase.update(HOURS_TABLE, cv, WHEREargs, null);
        //return ourDatabase.insert(DATE_TABLE, null, cv);
    }
}

Hi, Can you explain clearly, the problem is when you want to Update one row, two rows are Updated? Am I right — CompEng, Mar 23 '14 at 08:01
not exactly, i am editing my question now, the problem now is that i cant update a row that does not exist yet, so i am trying to figure out a way if that if can not update the row (because no row with that current date value exists), how can i insert a row and still return the values ? Thanks you for your help — 7888, Mar 23 '14 at 08:10
Yes, it does return the value, i am having problems coding whether to return an update or insert, how can you determine whether or not there are values that are being collected by the cursor? — 7888, Mar 23 '14 at 08:18
Can you look my answer and END Of the answer there is a link — CompEng, Mar 23 '14 at 08:35
haha sorry, i cant upvote yet without a certain reputation level, but i did accept your answer that you posted, big help — 7888, Mar 23 '14 at 08:43

score 0 · Accepted Answer · edited May 23 '17 at 10:31

0

I can not compile it Because I am on phone. You can run 2 Script I think First you can run Update **there is no problem with this Because if the rows does not exists there is nothing **

Secondly you can ru Insert Script with where clause the clause about the row not exists

When I go to home i can Write Script if you do not Write,

Can you look this link INSERT IF NOT EXISTS ELSE UPDATE?

edited May 23 '17 at 10:31

Community

1
1

answered Mar 23 '14 at 08:23

CompEng

7,161
16
68
122

The link you just sent was exactly what my code was missing, using insert or replace removes the need for the nested if loops (determining whether to update or insert) i just completed, i will refactor my code and update with answer when i can, Thankyou for your help – 7888 Mar 23 '14 at 08:42

SQLite Update and Increment with WHERE clause

1 Answers1