Getting Duplicate column name 'Unknown' error when executing insert statement , mysql

Question

Created a new DB schema / tables and tried to insert values. My insert query as follows

INSERT INTO `arms`.`lobalarmpriorities`(`id`,`alarmpriority`,`description`)
SELECT * FROM (SELECT 0,'Unknown', 'Unknown') AS tmp
WHERE NOT EXISTS(select id  FROM lobalarmpriorities WHERE id = 0) LIMIT 1;

INSERT INTO `arms`.`lobalarmpriorities`(`id`,`alarmpriority`,`description`)
SELECT * FROM (SELECT 1,'T1', 'T1 - critical – staffed') AS tmp
WHERE NOT EXISTS(select id  FROM lobalarmpriorities WHERE id = 1) LIMIT 1;

Here i get following error after my first insert query, but second insert query worked fine.;

What im doing wrong here?

Error Code: 1060. Duplicate column name 'Unknown'

Caius Jard · Accepted Answer · 2019-11-18 23:59:42Z

I'm guessing if you alias your columns differently it will work out:

INSERT INTO `arms`.`lobalarmpriorities`(`id`,`alarmpriority`,`description`)
SELECT * FROM (SELECT 0 as id,'Unknown' as a, 'Unknown' as b) AS tmp
WHERE NOT EXISTS(select id  FROM lobalarmpriorities WHERE id = 0) LIMIT 1;

The second query doesn't crash because the strings are different

You haven't provided an alias for the strings so my reasoning is that MySQL tries to help out by creating an alias from the string, but it creates identical aliases

SQLServer wouldn't let you do this; it would give a error that the column in the subquery needs an alias (and it wouldn't let you declare two column aliases that are the same). Aliases are required for columns in a sub query so that you can unambiguously refer to the columns in an outer query - think about this one:

SELECT x FROM(SELECT 1 as x, 'hello' as x)

What should MySQL show you? 1 or "hello"?

Hence the reason why subquery columns need unique aliases

nbk · Accepted Answer · 2019-11-18 23:53:32Z

1

You can do you need oly to add column names

INSERT INTO `arms`.`lobalarmpriorities`(`id`,`alarmpriority`,`description`)
SELECT * FROM (SELECT 0,'Unknown' test, 'Unknown' test1) AS tmp
WHERE NOT EXISTS(select id  FROM lobalarmpriorities WHERE id = 0) LIMIT 1;

answered Nov 18, 2019 at 23:53

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Comments

Simon R · Accepted Answer · 2019-11-18 23:54:25Z

1

When you run this line - SELECT * FROM (SELECT 0,'Unknown', 'Unknown') AS tmp - it's getting confused because it treats the string as a column name. To rectify you can change to;

INSERT INTO `arms`.`lobalarmpriorities`(`id`,`alarmpriority`,`description`)
SELECT * FROM (SELECT 0 as id,'Unknown' AS colA, 'Unknown' AS colB) AS tmp
WHERE NOT EXISTS(select id  FROM lobalarmpriorities WHERE id = 0) LIMIT 1;

answered Nov 18, 2019 at 23:54

Simon R

3,8024 gold badges34 silver badges44 bronze badges

Collectives™ on Stack Overflow

Getting Duplicate column name 'Unknown' error when executing insert statement , mysql

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related