ERROR 1060: Duplicate column name

[Lance Lovette]

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 <mysql-th...@lists.mysql.com>
To unsubscribe, e-mail <mysql-unsubscribe-myodbc=freebsd.csie...@lists.mysql.com>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

[Michael Widenius]

Hi!

>>>>> "Lance" == Lance Lovette <lance_...@hotmail.com> writes:

Lance> The following CREATE TABLE statements seem straight forward but they all
Lance> fail. Can someone explain why they fail and what I can do to get rid of the
Lance> "Duplicate column name" errors?

mysql> CREATE TEMPORARY TABLE User2 (UserID int(11)) SELECT UserID FROM

Lance> User;

Lance> ERROR 1060: Duplicate column name 'UserID'

The above is an correct error message.

What you are trying to do is the following:

CREATE TEMPORARY TABLE User2 (UserID int(11) not null, UserID int(11)) SELECT UserID FROM User;

This is because all columns from the SELECT is automaticly insert into
the table with the given column and type taken from the corresponding
column in the select statement.

This makes it trivial to quickly do things like:

CREATE TEMPORARY TABLE test (primary key (id)) SELECT ...many-fields....

Which will create a temporary table with all columns and have a
primary key on the column named 'id'.

Regards,
Monty

[Lance Lovette]

Thanks for the clarification. Is it a MySQL bug then that the CREATE query
converts all the VARCHAR fields into CHAR fields so the query 'CREATE
TEMPORARY TABLE User2 SELECT * FROM User' creates a temporary table much
larger than the User table?

Hi!

Regards,
Monty

<mysql-unsubscribe-lance-mysql=dailyra...@lists.mysql.com>

[Sinisa Milivojevic]

Lance Lovette writes:
> Thanks for the clarification. Is it a MySQL bug then that the CREATE query
> converts all the VARCHAR fields into CHAR fields so the query 'CREATE
> TEMPORARY TABLE User2 SELECT * FROM User' creates a temporary table much
> larger than the User table?
>

No, this is not a bug, this is a feature ........... ;o)

If you wish that VARCHAR are created instead of CHAR, then you should
add TYPE=PACKED in CREATE statement.>

--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic <sin...@mysql.com>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

[Lance Lovette]

TYPE=PACKED does not seem to be documented in Chapter 7.7 in the online
documentation. Where can I find a description of the PACKED option?

Also, can it be noted in the documentation that CREATE ... SELECT * FROM ...
does not create an exact copy of the table? Had I not been paying attention
I would not have noticed all the 320MB temporary tables I'd have been
creating.

[Sinisa Milivojevic]

Lance Lovette writes:
> TYPE=PACKED does not seem to be documented in Chapter 7.7 in the online
> documentation. Where can I find a description of the PACKED option?
>
> Also, can it be noted in the documentation that CREATE ... SELECT * FROM ...
> does not create an exact copy of the table? Had I not been paying attention
> I would not have noticed all the 320MB temporary tables I'd have been
> creating.
>

Sorry, what I meant was :

PACK_KEYS ....