Update table that created based on other tables (by UNION ALL)

Question

I've created a final table that fetched data from two other tables by UNION ALL statement with this Guide
Now I have new problem with updating table final.
When I want update "final_qty" , "firts_qty" or "second_qty" must be updated.
But I don't know how can I do it !
This is my codes:

// Table Names

public static final String TBL_FIRST = "table_first"; 
public static final String TBL_SECOND = "table_second"; 
public static final String TBL_FINAL = "table_final";

//Table Fisrt Columns

private static final String KEY_FIRST_ID = "first_id"; 
private static final String KEY_FIRST_PRODUCT = "first_product"; 
private static final String KEY_FIRST_QTY = "first_qty";

//Table Second Columns

private static final String KEY_SECOND_ID = "second_id"; 
private static final String KEY_SECOND_PRODUCT = "second_product"; 
private static final String KEY_SECOND_QTY = "second_qty";

//Table Final Columns

private static final String KEY_FINAL_ID = "final_id"; 
private static final String KEY_FINAL_PRODUCT = "final_product"; 
private static final String KEY_FINAL_QTY = "final_qty";

// Create Statements

private static final String CREATE_TABLE_FIRST = "CREATE TABLE " + TBL_FIRST + "(" + 
KEY_FIRST_ID+ " INTEGER PRIMARY KEY AUTOINCREMENT," + 
KEY_FIRST_PRODUCT + " TEXT," + 
KEY_FIRST_QTY + " INTEGER" + ")"; 

private static final String CREATE_TABLE_SECOND = "CREATE TABLE " + TBL_SECOND + "(" + 
KEY_SECOND_ID+ " INTEGER PRIMARY KEY AUTOINCREMENT," + 
KEY_SECOND_PRODUCT + " TEXT," + 
KEY_SECOND_QTY + " INTEGER" + ")"; 

private static final String CREATE_TABLE_FINAL = "CREATE TABLE " + TBL_FINAL + "(" + 
KEY_FINAL_ID+ " INTEGER PRIMARY KEY AUTOINCREMENT," + 
KEY_FINAL_PRODUCT + " TEXT," + 
KEY_FINAL_QTY + " INTEGER" + ")";

// INSERT DATA TO FINAL TABLE

"INSERT INTO " + TBL_FINAL + "(" + 
KEY_FINAL_PRODUCT + "," + KEY_FINAL_QTY + ")" + 
" SELECT " + KEY_FIRST_PRODUCT + "," + KEY_FIRST_QTY +  
" FROM " + TBL_FIRST + " WHERE " + KEY_FIRST_QTY + ">" + 0 + 
"UNION ALL SELECT " + KEY_SECOND_PRODUCT + "," + KEY_SECOND_QTY + 
" FROM " + TBL_SECOND + " WHERE " + KEY_SECOND_QTY + ">" + 0  ;

This is sample of my activities.

Sample image

In main activity I can go to first or second or final activities. When I'm in first or second activities, I change their items quantities and I return to the main activity.
Then I click on Final button in actionBar and go to Final Activity. In this Activity I'm going to change items quantities, so in addition to final_table, first_table and second_table items quantities must be updated.

Also When I go to my first activity and update "first_qty", in my "Final Table" I have two same rows with different qty.

Any offers would be appreciated.

@CL. Where u suggest define it ?! Please mention to my below answer descriptions. — Farshad Kazemi, Sep 27 '14 at 10:24
1 - Can't you use the `final table` **directly**? 2 - Try to use `INSERT OR REPLACE` instead of `INSERT` — Phantômaxx, Sep 27 '14 at 10:25
@FrankN.Stein, I can use final_table, but when I'm going to update its items quntities, items quantities of other tables don't update! — Farshad Kazemi, Sep 27 '14 at 10:36
And why do you need the other tables? OR why isn't the "final table" a **view**, instead? It would be always the mirror of the other two tables contents... — Phantômaxx, Sep 27 '14 at 10:41
I'm new in sqlite (and other databases) !!! Could you tell me how can I use a view instead final table ?! — Farshad Kazemi, Sep 27 '14 at 10:49
See the official docs: http://www.sqlite.org/lang_createview.html. Or see this for more detailed info: http://www.tutorialspoint.com/sqlite/sqlite_views.htm — Phantômaxx, Sep 27 '14 at 11:11
@FrankN.Stein, Tanks a lot for your documents. I'm reading them but as I understand, I can't update VIEW ... — Farshad Kazemi, Sep 27 '14 at 11:49
NO. A View is just to be READ. You only update the **original Tables**. And then see the results reflected in the View. — Phantômaxx, Sep 27 '14 at 11:51
If you check my image link, I need to update quantities in final activity. But in VIEW I can't do it. Also, how can I fetch deta of VIEW and show in ListView ?! — Farshad Kazemi, Sep 27 '14 at 12:29

Update table that created based on other tables (by UNION ALL)

0 Answers0