Error Code: 1060. Duplicate column name

Question

I've been receiving Error Code: 1060. :

• Duplicate column name 'NULL'
• Duplicate column name '2016-08-04 01:25:06'
• Duplicate column name 'john'

However, I need to insert some field with the same value, but SQL is denying and showing the above error. The error is probably sql can't select the same column name, in that case is there other way of writing the code? Below is my current code

INSERT INTO test.testTable SELECT * 
FROM (SELECT NULL, 'hello', 'john', '2016-08-04 01:25:06', 'john'
, '2016-08-04 01:25:06', NULL, NULL) AS tmp
WHERE NOT EXISTS (SELECT * FROM test.testTable WHERE message= 'hello' AND created_by = 'john') LIMIT 1

My Column:

• (id, message, created_by, created_date, updated_by, updated_date, deleted_by, deleted_date)

Please assist, thanks.

Answer 1

Your duplicate column names are coming from your subquery. You select null, john, and 2016-08-04 01:25:06 multiple times. Provide the columns you are selecting with names/aliases:

INSERT INTO test.testTable 
SELECT * 
FROM (SELECT NULL as col1, 'hello' as col2, 
       'john' as col3, '2016-08-04 01:25:06' as col4, 
       'john' as col5, '2016-08-04 01:25:06' as col6, 
       NULL as col7, NULL as col8) AS tmp
WHERE NOT EXISTS (SELECT * 
                  FROM test.testTable 
                  WHERE message= 'hello' AND created_by = 'john')
LIMIT 1

Not sure limit 1 is useful here, you are only selecting a single row to potentially insert.

Answer 2

You are using a subquery. Because you don't give the columns aliases, MySQL has to choose aliases for you -- and it chooses the formulas used for the definition.

You can write the query without the subquery:

INSERT INTO test.testTable( . . .)
    SELECT NULL, 'hello', 'john', '2016-08-04 01:25:06', 'john',
           '2016-08-04 01:25:06', NULL, NULL
    FROM dual
    WHERE NOT EXISTS (SELECT 1
                      FROM test.testTable tt
                      WHERE tt.message = 'hello' AND tt.created_by = 'john'
                     );

If you do use a subquery in the SELECT, then use correlation clauses in the WHERE subquery:

INSERT INTO test.testTable( . . .)
    SELECT * 
    FROM (SELECT NULL as col1, 'hello' as message, 'john' as created_by,
                 '2016-08-04 01:25:06' as date, 'john' as col2,
                 '2016-08-04 01:25:06' as col3, NULL as col4, NULL as col5
         ) t
    WHERE NOT EXISTS (SELECT 1
                      FROM test.testTable tt
                      WHERE tt.message = t.message AND
                            tt.created_by = t.created_by
                     );

In addition, the LIMIT 1 isn't doing anything because you only have one row.