I am trying to merge two tables with financial information about the same list of stocks: the first is the prices table (containing, daily, weekly, monthly, etc... price data) and the second is the ratios table (containing valuation and other ratios). Both tables have identical primary key numerical ID columns (referencing the same stock tickers). After creating a connection cursor cur, My code for doing this is:
CREATE TABLE IF NOT EXISTS prices_n_ratios AS SELECT * FROM
(SELECT * FROM prices INNER JOIN ratios ON prices.id = ratios.id);
DROP TABLE prices;
DROP TABLE ratios;
This works fine except that the new prices_n_ratios table contains an extra column named ID:1 whose name is causing problems during further processing.
How do I avoid the creation of this column, maybe by somehow excluding the second tables's first primary key ID column from * (listing all the column names is not an option), or if I can't, how can I get rid of this extra column from the generated table as I have found it very hard to delete it in SQLite3?
Just list all the columns you actually want in the SELECT clause, instead of using *.
Alternatively, join with the USING clause, which automatically removes the duplicate column:
SELECT * FROM prices JOIN ratios USING (id)