Complex Update Query with Nested Select Android SQLite

Question

android noob... I have two tables, with a one to many relationship between country_tbl and city_tbl, and I would like to concatenate values from city_tbl.landmark_col with GROUP_CONCAT() and INSERT all the landmark_col values as a single String into country_tbl.all_landmarks column. The SQL seems to require a nested SELECT to concatenate the landmark_col values before passing them to the country_tbl... something like:

UPDATE country_tbl 
SET country_tbl.all_landmarks = (SELECT landmarks_col FROM 
                                    (SELECT country_id, group_concat(landmarks_col) 
                                            FROM city_tbl INNER JOIN country_tbl
                                            ON country_tbl.country_id = city_tbl.country_id
                                            GROUP BY country_tbl.country_id) 
                                    AS country_landmarks      
                                WHERE country_tbl.country_id = country_landmarks.country_id)
WHERE
EXISTS (
    SELECT *
    FROM country_landmarks
    WHERE country_tbl.country_id = country_landmarks.country_id
);

Not sure if nested select statements are even supported or if just too resource intensive... there must be a better way, as it seems like using rawQuery is not the best solution. Not sure if I should be creating temporary tables, using ContentProviders, or passing a cursor...?

Answer 1

I answered this by splitting up the long SQL query into two parts. First I created a subquery with a SQLiteQueryBuilder and ran using rawQuery to get a two column cursor with the location_id and the group_concat values for the landmark_names. I was then able to cycle through the cursor to update the country table with each of the appropriate concatenated values of all the landmark names for that country.

The query below is a tad more complicated than the question above (which I simplified before posting), only because I had to join a landmarks list table with another landmark_type table by the landmark_type_id, and my real goals was to concatenate the shorter list of landmark_type by country, not the long list of all the landmark_names by country. Anyway, it works.

    public void UpdateCountryLandmarks() throws SQLException {
    Cursor c = null;
    String subquery = SQLiteQueryBuilder.buildQueryString(
            // include distinct
            true,
            // FROM tables
            LANDMARK_TYPE_TABLE + "," + LANDMARKS_TABLE,
            // two columns (one of which is a group_concat()
            new String[] { LANDMARKS_TABLE + "." + LOCATION_ID + ", group_concat(" + LANDMARK_TYPE_TABLE + "." + LANDMARK_TYPE + ",\", \") AS " + LANDMARK_NAMES },
            // where
            LANDMARK_TYPE_TABLE + "." + LANDMARK_ID + "=" + LANDMARKS_TABLE + "." + LANDMARK_TYPE_ID,
            // group by
            LANDMARKS_TABLE + "." + LOCATION_ID, null, null, null);

    c = mDb.rawQuery(subquery, null);

    if (c.moveToFirst()) {
        do {
            String locationId = c.getString(c.getColumnIndex(LOCATION_ID));
            String landmarkNames = c.getString(c.getColumnIndex(LANDMARK_NAMES));
            ContentValues cv = new ContentValues();
            cv.put(LANDMARK_NAMES, landmarkNames);
            mDb.update(COUNTRY_TABLE, cv, LOCATION_ID + "=" + locationId, null);
        } while (c.moveToNext());
    }

    c.close();

    }