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
