sqlite Update or insert

Question

I want to insert data into a table but if data exist I want to update but if one column is set to "1" I want that it updates some columns and others doesn't in order to not lose some state, anybody knows how to do it? triggers ?

If existe ( If columnA is 1 then update but keep columnA and ColumnB otherwise update al columns ) else insert

Please add your code, but with update you only need the id and the columns you want to change, insert needs all columns.' — Tomas, Mar 12 '14 at 19:17
Could you post what you have? This is a simple SQL query, updating the values simply requires a key to find the value in the database and then some SQL to update it. — zgc7009, Mar 12 '14 at 19:19
This is an extremely common problem, jsut by coming to this question the Related section of the site on the right lower part shows the relevant solutions: http://stackoverflow.com/questions/7180441/android-sqlite-update-insert?rq=1 also http://stackoverflow.com/questions/418898/sqlite-upsert-not-insert-or-replace?rq=1 — VM4, Mar 12 '14 at 19:29

score 1 · Answer 1 · answered Mar 13 '14 at 08:49

Just write the code as you've described it:

db.beginTransaction();
try {
    // SELECT columnA FROM MyTable WHERE Name = 'whatever'
    Cursor c = db.query("MyTable", new String[] { "columnA" },
                        "Name = ?", new String[] { "whatever" },
                        null, null, null);
    if (c.moveToFirst()) {
        if (c.getInt(0) == 1) {
            // update but keep columnA and columnB
            db.update(...);
        } else {
            // update all columns
            db.update(...);
        }
    } else {
        db.insert(...);
    }
    db.setTransactionSuccessful();
} finally {
    db.endTransaction();
}

score 0 · Answer 2 · answered Mar 12 '14 at 21:53

0

In SQLite you can only do that through an UPDATE trigger.

Of course you still have the option to do it at Java level: SELECT first then UPDATE or INSERT as needed.

answered Mar 12 '14 at 21:53

m0skit0

25,268
11
79
127

Could you point me out how to do the trigger statment and all sqlite versiones support triggers – Xenione Mar 12 '14 at 22:00
I'm 100% sure API 8+ (2.3+) supports it. About how to do the trigger, you already wrote the pseudo-code, so give it a go yourself ;) – m0skit0 Mar 12 '14 at 22:46

sqlite Update or insert

2 Answers2