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

Answer 1

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)

Answer 2

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.