SELECT IF EXISTS ELSE INSER, Mysql

Question

i have problems with my sql-statement and coulnd't find a solution to this in the already asked questions here.

my statement is:

IF EXISTS (SELECT LastLogin FROM user WHERE Email = @mail) 
SELECT LastLogin FROM user WHERE Email = @mail 
ELSE INSERT INTO 
`user`(LastLogin) VALUES ('00.00.0000') WHERE  Email = @mail 
END IF

tried:

CREATE Procedure lastlogin()
  begin
   IF EXISTS (SELECT LastLogin FROM fenutzer WHERE Email = @mail)  
      SELECT LastLogin FROM user WHERE Email = @mail
  ELSE
      INSERT INTO `user`(LastLogin) VALUES ('00.00.0000') WHERE  Email = @mail
 END IF;



call lastlogin()

LastLogin has the Datatype "DateTime".

i have the code inside a visual-studio-project (C#) inside an sql-command. i get the error that there is something wrong inside my sql-syntax (right syntax next to "else insert in"). but i cant figure out whats wrong

Is this code in a stored program (procedure, function,trigger,event)? — P.Salmon, Jan 09 '18 at 12:03
Possible duplicate of [Select if exist else insert?](https://stackoverflow.com/questions/18286532/select-if-exist-else-insert) — marekful, Jan 09 '18 at 12:05
Looks like you are trying to execute MSSQL syntax into MySQL.. MySQL will only supports `IF EXISTS(...) ELSE ..` syntax within procedures, functions, triggers en events. . — Raymond Nijland, Jan 09 '18 at 12:06
Yeah, what are you actually trying to do? I still don't get it. — Strawberry, Jan 09 '18 at 12:12
@RaymondNijland ive already tried this (see edit) but still get the same error that there something wrong in my sql syntax. (also tried putting delimiter$$ before it and after end $$ like in the from marekful suggested solution) — catcat, Jan 09 '18 at 12:17
@Strawberry so i want to read out the last time a user logged in (saved in LastLogin"), but if its the first time a user ever logged in than of course there is no entry in the table. so if thats the case i want to insert (00.00.0000) (need that later in my overall code) — catcat, Jan 09 '18 at 12:21
I'm not familiar with the technology you are using but I suspect you should be doing this in your application code rather than generating sql code. — P.Salmon, Jan 09 '18 at 12:29
Ok i see the procedure code @catcat.. i've posted a answer with correct MySQL's procedure syntax. — Raymond Nijland, Jan 09 '18 at 12:36

score 0 · Answer 1 · answered Jan 09 '18 at 12:57

Here's something to think about. I'm using a data set from a question posted earlier, but it works well enough for this purpose...

Consider the following data set...

SELECT * FROM my_table;
+----+-----------+
| id | member_id |
+----+-----------+
|  1 |         1 |
|  2 |         2 |
|  3 |         1 |
|  4 |         2 |
|  5 |         3 |
|  6 |         1 |
|  7 |         2 |
|  8 |         4 |
|  9 |         1 |
| 10 |         3 |
| 11 |         4 |
| 12 |         1 |
| 13 |         2 |
| 14 |         3 |
| 15 |         5 |
| 16 |         5 |
+----+-----------+

Let's say we want the latest id for a given member. We can do this...

SELECT CASE WHEN member_id = 5 THEN id ELSE 0 END last_id FROM my_table ORDER BY id DESC LIMIT 1;
+---------+
| last_id |
+---------+
|      16 |
+---------+

...and in the event that the member doesn't exist...

SELECT CASE WHEN member_id = 6 THEN id ELSE 0 END last_id FROM my_table ORDER BY id DESC LIMIT 1;
+---------+
| last_id |
+---------+
|       0 |
+---------+

SELECT IF EXISTS ELSE INSER, Mysql

1 Answers1