CODEWITHSUNDEEP
androidsqlite
ADK
ADK

Reputation: 139

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

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.

Upvotes: 2

Views: 4979

Answers (2)

Mayank
Mayank

Reputation: 8852

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.

Upvotes: 3

Davek804
Davek804

Reputation: 2804

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
    }

Upvotes: 1

Related Questions