2

I really don't understand why my statement:

IF EXISTS (SELECT * FROM people WHERE ID = 168)
THEN SELECT * FROM people
END IF;

is returning this error:

Unknown statement type. (near "IF EXISTS" at position 0)

I'm using MariaDB 10.3. Any ideas?

ADDITIONAL INFO

This is of course a simplified example. What I wanna do is, concretely:

IF EXISTS (SELECT * FROM people WHERE ID = 168)
THEN UPDATE people SET calculated_value = complex_queries_and_calculations
WHERE ID = 168

.., so to update a field of a given record if that record contains a given data, and else do nothing. To generate the data which would be used for the update, I need to query other tables for values and make some calculations. I want to avoid these queries + calculations, if there's actually nothing to update. And in this case, simply do nothing. Hence, I guess that putting for example an EXIST clause inside a WHERE clause of the UPDATEstatement would end in many queries and calculations made in vain.

DevelJoe
  • 856
  • 1
  • 10
  • 24

1 Answers1

3

MySQL and MariaDB only allow IF statements in programming blocks -- stored functions, procedures, and triggers.

Instead, just use:

select p.*
from people p
where exists (select 1 from people p2 where p2.id = 168);

This returns all people if id 168 is in table.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • It's really disappointing that mariadb's doc doesn't actually mention such basics... (see https://mariadb.com/kb/en/if/). Thanks, but this does not solve my problem. I only want to update a table (I simplified my example) if the given record of the specified ID is present in the database (and else do nothing). In other words, I wanna use IF EXISTS to short-circuit my statement, and only proceed to the update statement if the concerned record has been found. For that case, this answer is not the right solution, am I right? You may have an idea how to short-circuit with IF EXISTS or CASE WHEN? – DevelJoe Nov 14 '20 at 17:36
  • @DevelJoe . . . This answers the question that you have asked here. If you have a different question it should be asked as a *new* question, not by evolving this question. That said, an `update` can have the same `where` clause. – Gordon Linoff Nov 14 '20 at 17:56
  • It did answer the syntax problem of my question yes, and thanks for that, but not it's logical purpose; because my code attempts to short-circuit the execution of the select statement (sets the condition before the statement), while your answer replaced a short-circuitting statement with a simple ```WHERE``` clause, which thus changes the whole logic of the game.. (or am I wrong and do ```WHERE``` clauses do short-circuit statement executions) ? I mean don't get me wrong, no critics at all to you, rather precising my intentions, and why I used ```IF EXISTS``` and not ```WHERE```.. – DevelJoe Nov 14 '20 at 18:16