insert record if not exists in sql, duplicate column name

Question

i wanted a solution to insert a record if it isn't there so i searched here and found a solution but i have another problem

INSERT INTO closed_answers (question_id, subject_id)
SELECT * FROM (SELECT 2, 2) AS tmp
WHERE NOT EXISTS (
    SELECT question_id FROM closed_answers WHERE question_id = 2 AND subject_id = 2
) LIMIT 1

the output is

#1060 - Duplicate column name '2'

if i used any 2 numbers that aren't identical it will work but the problem arise when the 2 numbers are the same

score 18 · Accepted Answer · answered Jul 27 '10 at 17:48

The smallest change to make your SQL work is to add aliases to your select statement:

INSERT INTO closed_answers (question_id, subject_id)
SELECT * FROM (SELECT 2 AS question_id, 2 AS subject_id) AS tmp
WHERE NOT EXISTS (
    SELECT question_id
    FROM closed_answers
    WHERE question_id = 2 AND subject_id = 2
) LIMIT 1

However if you have a unique constraint on (question_id, subject_id) then you can use INSERT IGNORE instead:

INSERT IGNORE INTO closed_answers (question_id, subject_id)
VALUES (2, 2)

This very much helped me here: http://stackoverflow.com/questions/3164505/mysql-insert-record-if-not-exists-in-table/3164741#comment33044636_3164741 — Avatar, Feb 17 '14 at 18:06

Fosco · Answer 2 · 2010-07-27T18:07:06.237

4

INSERT INTO closed_answers (question_id, subject_id) 
SELECT * FROM (SELECT 2 a, 2 b) AS tmp 
WHERE NOT EXISTS ( 
    SELECT 1 FROM closed_answers WHERE question_id = 2 AND subject_id = 2 
) LIMIT 1

Your select statement with the subquery is strange an unnecessary, but the problem was that you did not name the columns being selected. When using exists, it is sufficient to just select 1, rather than a field. Also the limit 1 was not necessary.

edited Jul 27 '10 at 18:07

answered Jul 27 '10 at 17:51

Fosco

36,451
6
79
100

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE NOT EXISTS ( SELECT 1 FROM closed_answers WHERE question_id = 2 AND subje' at line 3 – Mark Byers Jul 27 '10 at 18:04
good call Mark.. I removed that second example, which was pretty brain dead silly of me. – Fosco Jul 27 '10 at 18:07
@MarkByers In my case, your answer cause the syntax error. But the Fosco's answer doesn't have any error. I am using MariaDB. – Yi Jiang Mar 07 '16 at 04:09

insert record if not exists in sql, duplicate column name

2 Answers2