"如果存在"查询接近 "IF" 时出错

Question

我在"IF"附近收到以下查询的语法错误：

myDataBase.execSQL("IF EXISTS (SELECT * FROM ANSWERS WHERE QUESTION_ID=" + id +" AND PLAYER ='" + player + "')" +
                "UPDATE ANSWERS SET "+field+"=1"
                + " WHERE QUESTION_ID=" + id + " AND LEVEL =" + level +" AND PLAYER='" + player + "' ELSE "
                + "INSERT INTO ANSWERS (level, player, question_id, "+field+") VALUES (level, player, id, 1)");

不知道我哪里出错了..有人看到了吗？

 near "IF": syntax error: IF EXISTS (SELECT * FROM ANSWERS WHERE QUESTION_ID=6 AND PLAYER ='nob')UPDATE ANSWERS SET showhint1=1 WHERE QUESTION_ID=6 AND LEVEL =1 AND PLAYER='nob' ELSE INSERT INTO ANSWERS (level, player, question_id, showhint1) VALUES (level, player, id, 1)

Answer 1

请参阅此问题的答案：

select exists(
    select * from answers ...
);

Answer 2

编辑：我喜欢对原始答案的评论：sackoverflow.com/questions/418898/... – Zapl 7分钟前。 我不知道你能做到这一点。 看起来非常高效。

我做得很快。 希望你明白这个想法...所以这些变量和东西将在非SQL代码中。 如果可以的话，我建议使用存储过程或类似的东西......或者完全分离出所有部分（查看它是否存在，基于此执行此 SQL，或者如果它不执行此 SQL）。

仅使用更新语句....如果表中不存在变量，则不会更新任何内容。 并且只用插入语句...如果表中确实存在变量，则不会插入任何内容。 除了这两个陈述之外，不需要有一个if或任何东西来实际检查答案中是否存在某些内容。

create table #answers (question_id int, player int, level int, other_field int) 
insert into #answers values (1,1,1,1) 
insert into #answers values (2,1,1,1)  

declare @question_id int
declare @player int
declare @level int
declare @other_field int
set @question_id=1
set @player=1 
set @level=1
set @other_field=1
-- if it exists already 
update a
    set     other_field=@other_field  
    from    #answers as a
    where   QUESTION_ID=@question_id and 
            PLAYER=@player and 
            other_field<>@other_field 

set @question_id=4
set @player=4
set @level=1
set @other_field=1

-- if it doesn't exist
insert into #answers (question_id, player, level, other_field)
        select  x.QUESTION_ID, x.player, @level, @other_field
        from    #answers a 
                right outer join 
                    (select @question_id as QUESTION_ID,
                            @player as player) as x 
                    on x.QUESTION_ID=a.QUESTION_ID and 
                        x.player=a.player
        where   a.player is null and a.question_id is null 

或者这个如果它不存在（更混乱但更短）

-- if it doesn't exist
insert into #answers (question_id, player, level, other_field)
        select  distinct  @QUESTION_ID, @player, @level, @other_field  
        from    #answers 
        where   not exists (select 1 from #answers where 
                                QUESTION_ID=@question_id and 
                                    PLAYER=@player )

Answer 3

If exists 不是在 SQL 更新语句中使用的有效关键字。