How to update multiple sqlite rows based on query results (Android, SQLITE)

Question

I have executed a rawQuery in android that returns multiple results that point to different rows.

example:  Cursor cursor = db.rawQuery("Select id as _id, List_id, Street_id FROM Streets WHERE Street_id = 5, null);

In my table, this would return two rows:

List_id = 2, Street_id = 5 
List_id = 7, Street_id = 5

I then want to update a column named Counter in a separate table for the two rows that List_id 2 and List_id 7 would correspond too (which would be _id = 3 and _id = 8) in my other table.

I understand I will have to do some cursor management.but I would really appreciate it if someone could give me some general basics to get started - I have been struggling on this pretty hard.

BETTER EXAMPLE:

I query my Streets table for all records where my Street_id = 5. I get two results. Within those returned rows, I have a column called List_id. The data in the two List_id columns are 2, and 5. I need to then update a field named Counter my Lists table based on the _id that is equal to 2 and 5.

Mayank · Accepted Answer · 2012-04-01 02:44:32Z

3

it looks like all you gotta do is have where clause in your update query also.

String filter = "Street_id=" + 5; 
ContentValues args = new ContentValues(); 
args.put(Counter, "value"); 
myDB.update("Streets", args, filter, null);

that should do the job

EDIT

db.rawQuery("update Streets (Counter) Set (Value) Where Street_id=5, (Value) Where Street_id=5");

this will update value in two rows, you can dynamically generate this query depending upon how many row's previous query returns.

EDIT

ok, let's try

db.rawQuery("update Lists (Counter) Set (Value) Where _id=2, (Value) Where _id=5");

please explain with example if that's not what you want or where you are having trouble with

above query will update Counter column in Lists table with Value where first row's _id = 2 and second row's _id=5.

edited Apr 1, 2012 at 2:44

answered Apr 1, 2012 at 1:35

Mayank

8,8424 gold badges38 silver badges63 bronze badges

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

6 Comments

ADK Over a year ago

Actually I mistyped -see my edits above. I'm actually trying to update multiple rows in a different tabled based on the results I got in my initial query.

ADK Over a year ago

I'm not sure I understand (sorry). Basically I need to take my two List_id results from above out of my STREETS table, and then update my LISTS table for the two rows that would correspond to List_id. (The values in my List_id field in STREETS is equivalent to my _id field in LISTS). I'm sure I am explaining it poorly :)

ADK Over a year ago

I tried to add a better example above in my orig post. I really appreciate the help as well.

Mayank Over a year ago

according to your example my second query shpuld work just fine

ADK Over a year ago

AH, I see now. Now, in your example you are actually writing out "2" and "5". How can I store those values based on my previous query to get the data in the first place? Currently I'm only able to store the first one, which is "2". the "5" is ignored.

|

Davek804 · Accepted Answer · 2012-04-01 02:09:02Z

public class DatabaseHandler extends SQLiteOpenHelper {

    // All Static variables
    // Database Version
    private static final int DATABASE_VERSION = 1;

    // Database Name
    private static final String DATABASE_NAME = "android_api";

    // Login table name
    private static final String TABLE_LOGIN = "login";

    // Login Table Columns names
    private static final String KEY_ID = "id";
    private static final String KEY_NAME = "name";
    private static final String KEY_EMAIL = "email";
    private static final String KEY_UID = "uid";
    private static final String KEY_CREATED_AT = "created_at";

    public DatabaseHandler(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    // Creating Tables
    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_LOGIN_TABLE = "CREATE TABLE " + TABLE_LOGIN + "("
                + KEY_ID + " INTEGER PRIMARY KEY,"
                + KEY_NAME + " TEXT,"
                + KEY_EMAIL + " TEXT UNIQUE,"
                + KEY_UID + " TEXT,"
                + KEY_CREATED_AT + " TEXT" + ")";
        db.execSQL(CREATE_LOGIN_TABLE);
    }
    public void addUser(String name, String email, String uid, String created_at) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_NAME, name); // Name
        values.put(KEY_EMAIL, email); // Email
        values.put(KEY_UID, uid); // Email
        values.put(KEY_CREATED_AT, created_at); // Created At

        // Inserting Row
        db.insert(TABLE_LOGIN, null, values);
        db.close(); // Closing database connection
    }

Collectives™ on Stack Overflow

How to update multiple sqlite rows based on query results (Android, SQLITE)

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related