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

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' 

Answer

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