Ryan,
Thanks for your reply. As I mention in my last post:
I got it sorted out I ended up with this using my SQLite Expert
Professional (if it matters).
update tableB set column2 = (select column2 from tableA where
tableA.rowid = tableB.rowid)
Of course I did not figure that out myself and asked for help on the
forum of the software and got an answer within 30 minutes.
---------
But I will keep this info for testing to see if it does the same thing
or can help me in the future. I tried to learn DB with ACCESS years ago
and never really fully understood it. I would get stuck on the
relationship part with the keys and the visual flow charts always seemed
to point from one table to another but the lines pointed to an item
instead of an ID. Or so it looked like that to me. Never really got to
use it back then. Now I am starting over again with SQLite. And I have
made some progress, but have a long way to go. Good thing for me is I
plan to use my database (for now) as basically a "flat file" where my
app will just use the related data in click events to further process.
Thanks again,
schemer
On 3/7/2015 1:42 PM, R.Smith wrote:
>
>
> On 2015-03-07 06:42 PM, Dave wrote:
>> I am fairly new at this although I have wanted to learn and tried
>> again and again...But I have a problem. I created a database and
>> probably did it wrong and I am trying to fix it. I made a database
>> with 7 tables in it all with a primary key and a record ID that
>> matches the primary key. Now when trying to use the database I see
>> that I should have made 1 table with all the related data (I think)
>> and am trying to copy one column of data at a time to the "main"
>> table. Can that be done and if so how? The data in all the columns
>> has to line up with the ID numbers. I know I can retype it all
>> manually but it seems it should be an easy task. I have SQLite Expert
>> Professional.
>
> Hi Dave, you did not give us the schemata so I'm going to guess you
> have tables like this:
>
> CEATE TABLE T1("ID" INT PRIMARY KEY, "val1" TEXT);
> CEATE TABLE T2("ID" INT PRIMARY KEY, "val2" TEXT);
> CEATE TABLE T3("ID" INT PRIMARY KEY, "val3" TEXT);
> ... all the way to ...
> CEATE TABLE T7("ID" INT PRIMARY KEY, "val7" TEXT);
>
> And now you figured out best would be to have 1 Table like this"
>
> CEATE TABLE T_ALL("ID" INT PRIMARY KEY, "val1" TEXT, "val2" TEXT,
> "val3" TEXT, "val4" TEXT, "val5" TEXT, "val6" TEXT, "val7" TEXT);
>
> Right?
>
> If this is the case, assuming[1] all ID's are present in all tables,
> the query to put all the single tables into the big one is simply this:
>
> INSERT INTO T_ALL (ID, val1, val2, val3, val4, val5, val6, val7)
> SELECT T1.ID, T1.val1, T2.val2, T3.val3, T4.val4, T5.val5, T6.val6,
> T7.val7
> FROM T1
> LEFT JOIN T2 ON T2.ID=T1.ID
> LEFT JOIN T3 ON T3.ID=T1.ID
> LEFT JOIN T4 ON T4.ID=T1.ID
> LEFT JOIN T5 ON T5.ID=T1.ID
> LEFT JOIN T6 ON T6.ID=T1.ID
> LEFT JOIN T7 ON T7.ID=T1.ID
> WHERE 1;
>
> This simply looks up the same ID in all the tables and inserts each
> tables value for the val column into the main table. You will have to
> (obviously) substitute your actual table/column names.
>
> [1] : If all the IDs are not present in all the columns, you will get
> NULL values inserted in the main table or completely miss out some IDs
> (if not present in T1), so if this is the case, let us know the exact
> schema and layout to figure out a more correct transfer.
>
> HTH
> Ryan
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
