I am trying to create a new table from an old table so I can remove some duplicates and implement a composite primary key. I have created the new table from the old one using LIKE, so the columns should be identical.
Why does this not work?
INSERT INTO PricesNEWtest (Global_MarketCap,pkey)
VALUES ((SELECT max(Global_MarketCap), pkey
FROM Prices GROUP BY pkey));
Error generated:
Error Code: 1136. Column count doesn't match value count at row 1
The example above only has two rows so it's more legible, but in reality the tables contain 15 columns, this is the full INSERT INTO statement, which generates the same errror:
INSERT INTO PricesNEWtest (Global_MarketCap,Global_Volume24h,BTC_Dominance,Rank,Name,
Symbol,ChangePerc24h,Price,BTC_Price,MarketCap,Volume24h,DateTime,Date,pkDummy,pkey)
VALUES ((SELECT max(Global_MarketCap), max(Global_Volume24h), max(BTC_Dominance), max(Rank), max(Name),
max(Symbol), max(ChangePerc24h), max(Price), max(BTC_Price), max(MarketCap), max(Volume24h),
max(DateTime), max(Date), max(pkDummy), pkey
FROM Prices GROUP BY pkey));
I added the double brackets for VALUES because without it I get error code 1064, but I don't fully understand why the double brackets are necessary. I am grouping by the pkey field (which currently has some duplicates I want to delete), which means I need to summarize the rest of the fields. The SELECT statement works fine on its own as you can see from the screenshot.
Is there another way to do this that I could try? Or is there an easier way to remove the duplicates directly from the original table?
I am using MySQL 5.7.14
Any help would be appreciated!
You just have the wrong syntax to INSERT
with a SELECT
statement:
INSERT INTO PricesNEWtest (Global_MarketCap, pkey)
SELECT max(Global_MarketCap), pkey
FROM Prices
GROUP BY pkey