nicker nicker - 4 months ago 44
MySQL Question

Error Code: 1060. Duplicate column name

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

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.

Comments