Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

sql - android update database column based on the current column value

In android, SQLiteDatabase has a update function

update(String table, ContentValues values, String whereClause, String[] whereArgs)

new values in put in values
If I want to update a column A by adding one to it, how should I prepare the ContentValues values variable? I don't think the following would work.

cv.put("A", "A" + 1);

I can sure run execSQL with raw sql, but it does not return num of row updated

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

If you'd execute a raw query, something like this should work to increment the current value in the column:

UPDATE table_name SET column_a = column_a + 1 WHERE _id = 1

(where 1 is just an example to illustrate how to apply it to a specific row)

The same probably wouldn't work with ContentValues, since (as the name indicates) it takes the values to set the column to. That means it needs to have been evaluated before building the ContentValues, whereas with a raw query the value isn't evaluated until the query actually runs on the database.

You can of course retrieve the current value first and then increment that accordingly when issuing an update; that requires a select query first. Quite commonly though, you're working with objects in Java, where the column value for a row is bound up to a member field of the object. If you've got a setup like that, then you probably already have the current value at the moment you want to run an update query.

As such, it would just look somewhat like:

SomeObject object = ...;
cv.put("column_a", object.getSomeValue() + 1);

(where I'm assuming object.getSomeValue() will return an int)


// edit: here's some more examples for the raw query approach:


// edit2: You've edited your original question and added:

I can sure run execSQL with raw sql, but it does not return num of row updated

If knowing how many rows the query changed is a must, then you can potentially leverage the changes() function. It still means you're going to have to run a second query though.

SELECT changes() FROM table_name

The docs say:

The changes() function returns the number of database rows that were changed or inserted or deleted by the most recently completed INSERT, DELETE, or UPDATE statement, exclusive of statements in lower-level triggers. The changes() SQL function is a wrapper around the sqlite3_changes() C/C++ function and hence follows the same rules for counting changes.

Alternatively, you could look into the rawQuery() method that takes an SQL statement and returns the result as a Cursor. Not sure if it that even works for an update query, or whether the result would be anything sensible, but if you're really lucky, you may find that Cursor.getCount() gives you the number of affected rows.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...