MoreScratch MoreScratch - 28 days ago 4
MySQL Question

Insert record if not exists in table failing in MySQL

Here is my query:

INSERT INTO table (id, actions, date, comments, type, url, rating)
SELECT * FROM (SELECT 'b7d54d99bf11', 'Information Exchanged', '1430463600', '', 'routine', 'http://example.com', '') AS tmp
WHERE NOT EXISTS (SELECT url FROM table WHERE url = 'http://example.com')


I get a SQL Error that says
Duplicate column name: ''
because there is no
rating
or
comments
passed in for some records.

How can I avoid the error? Is there a better way to achieve this?

Answer

You have to include alias for your columns

INSERT INTO table (id, actions, date, comments, type, url, rating)
  SELECT * FROM (SELECT 'b7d54d99bf11' as id, 
                        'Information Exchanged' as actions, 
                        '1430463600' as date, 
                        '' as comments, 
                        'routine' as type, 
                        'http://example.com' as url, 
                        '' as rating) AS tmp
Comments