开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜