## query_script_expressions

QueryScript Expressions: conditional truth values

### SYNOPSIS

while(expression) { if (expression) statement; }

### DESCRIPTION

*Expressions* are truth valued clauses. QueryScript accepts any valid SQL expression,
and adds additional particular cases.

Expressions are used by flow control structures: if-else, while, loop-while.

#### Standard SQL expressions

Any expression on which **SELECT expression IS TRUE** can be used as
a QueryScript expression. The following are examples of valid expressions:

- TRUE
- NULL
- 0
- 4 < 5
- @x < 5
- (@x = 5) OR (COALESCE(@y, @z) BETWEEN 10 AND 20)
- @n IN (SELECT name FROM world.City)
- SELECT COUNT(*) > 100 FROM world.Country WHERE Continent='Africa'

#### QueryScript valid expressions

In addition to any standard SQL expression, QueryScript also acknowledges the following statements as valid expressions:

- INSERT [IGNORE]
- INSERT .. ON DUPLICATE KEY UPDATE
- UPDATE [IGNORE]
- DELETE [IGNORE]
- REPLACE

**TRUE**value, when the number of rows affected by the DML query is non-zero. In particular, the value of

**ROW_COUNT()**is examined.

For example, consider the following:

The above query does not actually delete any row; hence its truth value ismysql> DELETE FROM world.Country WHERE Continent='flatlandia' Query OK, 0 rows affected (0.00 sec)

**FALSE**.

Notes:

- A standard
**INSERT**makes no sense to use, since it will either succeed (resolving to**TRUE**) or completely fail, aborting the evaluation. It only makes sense to use**INSERT IGNORE**or**INSERT .. ON DUPLICATE KEY UPDATE**. **REPLACE**always succeeds, and so will always resolve to**TRUE**. It is included for completeness.

### EXAMPLES

**DELETE** statement as *expression*; delete all 'Asia' records in small chunks:

while (DELETE FROM world.Country WHERE Continent = 'Asia' LIMIT 10) { do sleep(1); }

**SELECT** and **INSERT** statements as *expressions*:

if (SELECT COUNT(*) > 0 FROM world.Country WHERE Continent = 'Atlantis') { INSERT INTO weird_logs VALUES ('Have found countries in Atlantis'); if (DELETE FROM world.Country WHERE Continent = 'Atlantis') INSERT INTO weird_logs VALUES ('And now they''re gone'); }

Simple arithmetic expression: generate Fibonacci sequence:

var $n1, $n2, $n3, $seq; set $n1 := 1, $n2 := 0, $n3 := NULL; set $seq := ''; loop { set $n3 := $n1 + $n2; set $n1 := $n2; set $n2 := $n3; set $seq := CONCAT($seq, $n3, ', '); } while ($n3 < 100); SELECT $seq AS fibonacci_numbers; +---------------------------------------------+ | fibonacci_numbers | +---------------------------------------------+ | 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89, 144, | +---------------------------------------------+