The following CREATE TABLE statements seem straight forward but they all
fail. Can someone explain why they fail and what I can do to get rid of the
"Duplicate column name" errors?
mysql> CREATE TEMPORARY TABLE User2 (UserID int(11)) SELECT UserID FROM
User;
ERROR 1060: Duplicate column name 'UserID'
I could use 'CREATE TEMPORARY TABLE User2 SELECT UserID FROM User' instead,
which in this case should produce the same result, but that doesn't help
because the query I really want to execute is much larger...
mysql> CREATE TEMPORARY TABLE User2 (UserID int(11), Email varchar(64),
Nickname varchar(32), FirstName varchar(32), LastName varchar(32), URL
varchar(255), DisabledDate datetime, DisabledReason tinyint(4), DeliveryMode
varchar(16), Reference varchar(255), RegisterDate datetime, LastVisitDate
datetime, TimeZoneID int(11), Password varchar(32), OptIn tinyint(4),
ZipCode varchar(32), Birthday date, LastUserAgent varchar(128), Visits
int(11), Setting int(11), BrandID int(11), XMailer varchar(100),
LastMailAgent varchar(100), LastMailAgentDate datetime) SELECT UserID,
Email, Nickname, FirstName, LastName, URL, DisabledDate, DisabledReason,
DeliveryMode, Reference, RegisterDate, LastVisitDate, TimeZoneID, Password,
OptIn, ZipCode, Birthday, LastUserAgent, Visits, Setting, BrandID, XMailer,
LastMailAgent, LastMailAgentDate FROM User;
ERROR 1060: Duplicate column name 'UserID'
The reason I want to execute this query instead of 'CREATE TEMPORARY TABLE
User2 SELECT * FROM User' is because the resulting data file (User2.MYD) is
306 MB in size, nearly 5 times larger than the original User.MYD, because
the CREATE query converts all the VARCHAR fields into CHAR fields.
I am using MySQL 3.23.39 on RedHat 6.2.
Thanks!
Lance
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
