insert record if not exists in sql, duplicate column name
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
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)
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.
精彩评论