sql SELECT in IF EXISTS

Question

I'm trying to run this :

IF EXISTS (SELECT id_commande FROM historique WHERE id_commande = 57 AND device = 70635601)
BEGIN
    SELECT id_commande FROM historique WHERE id_commande = 57 AND device = 70635601
END
ELSE
BEGIN
    SELECT (MAX(id_commande)+1) FROM historique
END

I hope you can understand what I want to do. My problem is that it always gives me back Erreur dans la requête (1064): Syntax error near 'BEGIN SELECT 1 END ELSE BEGIN SELECT 2 END' at line 2.

Thank you for helping me !

score 2 · Accepted Answer · answered Dec 22 '19 at 09:07

You can do it without EXISTS by using COALESCE():

SELECT 
  COALESCE(
    (SELECT id_commande FROM historique WHERE id_commande = 57 AND device = 70635601),
    (SELECT MAX(id_commande) + 1 FROM historique)
  )

I assume that the 1st query would return only 1 row.

score 0 · Answer 2 · answered Dec 22 '19 at 08:24

0

I think you are trying to use a CASE statement:

SELECT CASE WHEN EXISTS (SELECT id_commande FROM historique WHERE id_commande = 57 AND device = 70635601) THEN 1 ELSE 2 END;

answered Dec 22 '19 at 08:24

richyen

8,114
4
13
28

score 0 · Answer 3 · edited Jun 20 '20 at 09:12

You can LEFT JOIN to a sub-query with values.
Then COALESCE the value or max.

For example:

create table historique 
(
   id int primary key auto_increment,
   device int,
   id_commande int
);

insert into historique 
(device, id_commande) values
(70635601, 57), 
(70635601, 63);

Query:

SELECT 
coalesce(
   max(case 
       when val.id_commande = hist.id_commande 
       then hist.id_commande 
       end),
   max(hist.id_commande)+1,
   1
) as id_commande
FROM (
  select 
  70635601 as device,
  57 as id_commande
) val
LEFT JOIN historique hist
ON hist.device = val.device
GROUP BY val.device

| id_commande |
| ----------: |
|          57 |

db<>fiddle here

sql SELECT in IF EXISTS

3 Answers3