Error near "IF" with If Exists query

Question

I get an syntax error near "IF" with following query:

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)");

Don't have an idea where I am going wrong though.. Somebody sees it?

 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)

Remember that Android uses just SQLite, see [syntax](http://www.sqlite.org/lang.html). There is AFAIK no way to do an update or insert in one statement. — zapl, Nov 16 '12 at 19:54
see also http://stackoverflow.com/questions/418898/sqlite-upsert-not-insert-or-replace — zapl, Nov 16 '12 at 20:06
@zapl thanks, i tried this structure: Cursor c = myDataBase.rawQuery( "SELECT..."); myDataBase = this.getWritableDatabase(); if(c != null){ myDataBase.execSQL("UPDATE..."); } else { myDataBase.execSQL("INSERT..."); } But it doesn't seem to be working.. I think it is correct though? I don't get an error, it just doesn't write to my DB! — Matthias Vanb, Nov 17 '12 at 10:25
Try changing `if(c != null)` to `if(c != null && c.getCount() > 0)`, `Cursor` should almost never be `null` http://stackoverflow.com/a/7223061/995891 — zapl, Nov 17 '12 at 11:57

score 1 · Answer 1 · edited May 23 '17 at 12:27

1

See the answer to this question:

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

edited May 23 '17 at 12:27

Community

1
1

answered Nov 16 '12 at 18:56

Ridcully

23,362
7
71
86

1

But I think the difference here is that I have to execute another statement if it doesn't exist.. am I just able to add an Else statement behind it? It is Insert or Update... – Matthias Vanb Nov 16 '12 at 19:06

gloomy.penguin · Answer 2 · 2012-11-16T20:15:15.770

edit: i like this comment on the original answer: sackoverflow.com/questions/418898/… – zapl 7 mins ago. I didn't know you could do that. It's very efficient looking....

i did this quick. hopefully you get the idea... so those variables and stuff would be in the non sql code. i suggest using a stored procedure or something like that if you can.... or separate out all the sections entirely (see if it exists, based on that do this sql, or if it doesn't do this sql).

with just the update statement.... nothing will update if the variables don't exist in the table. and with just the insert statement... nothing will insert if the variables do exist in the table. besides those two statements, there doesn't need to be an if or anything to actually check to see if something exists in answers or not.

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

or this for the if it doesn't exist (messier but shorter)

-- 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 )

I also have been thinking about it.. Anyway, my conclusion was that I wanted it in a shorter way... I came up with this : Cursor c = myDataBase.rawQuery( "SELECT..."); myDataBase = this.getWritableDatabase(); if(c != null){ myDataBase.execSQL("UPDATE..."); } else { myDataBase.execSQL("INSERT..."); } But as I say above, it doesn't seem to write to my DB, which is pretty strange I think.. it seems correct, no? — Matthias Vanb, Nov 17 '12 at 10:28

Sazzadur Rahaman · Answer 3 · 2013-05-11T03:09:45.573

0

If exists is not valid keyword to use in SQL update statements.

edited May 11 '13 at 03:09

answered Nov 16 '12 at 19:23

Sazzadur Rahaman

6,938
1
30
52

Error near "IF" with If Exists query

3 Answers3