CODEWITHSUNDEEP
mysqlmariadb
art vanderlay
art vanderlay

Reputation: 2463

mysql test IF variable IS NULL (or empty)

we are selecting a row in mysql/mariadb and storing chosen columns in variables. After this, using IF we would like to test one of the variables and see if it has been set or is null, if it is then we assign it a value and continue on.

Using IS NULL does not seem to work on a non expression.

select id,history,active,jsonorder
INTO @id,@history,@active,@jsonorder
from myTable where uid = myUid
delimiter |
IF @jsonorder IS NULL THEN
  @myNewVal="zzz";
ELSE
  @myNewVal="yyy";
END IF|
insert into otherTable (colA) VALUES (@myNewVar);

What is the correct way to test if the select has provided a value into a variable such as @jsonorder?

Upvotes: 1

Views: 5720

Answers (3)

Rick James
Rick James

Reputation: 142433

The IF statement can only be used in Stored Routines. The IF function can be used virtually anywhere an expression can be put:

select id,history,active,jsonorder
    INTO @id,@history,@active,@jsonorder
    from myTable
    where uid = myUid;
insert into otherTable (colA)
    VALUES (IF(@jsonorder IS NULL, "zzz", "yyy"));

Where does myUid come from?

Is otherTable only one column wide? Or do all the other columns have defaults?

(I'm worried that you over-sanitized the question.)

Upvotes: 1

spencer7593
spencer7593

Reputation: 108480

We could use an expression in the SELECT list: 

SELECT t.id
     , t.history
     , t.active
     , IFNULL(t.order,1) AS `order`
  INTO @id
     , @history
     , @active
     , @order
  FROM `myTable` t
 WHERE t.uid = ...

Note that DELIMITER is not a SQL statement; it's command recognized by the mysql command line client (and some other clients).

documented here in MySQL Reference Manual: https://dev.mysql.com/doc/refman/8.0/en/mysql-commands.html

It's not clear in what context this script is being run; is this part of a MySQL stored program, or being called application code. What we are actually trying to achieve?

There are several other expressions we could use in the SELECT list, e.g. 

CASE WHEN t.order IS NULL THEN 1 ELSE t.order END`

or 

IF(t.order IS NULL,1,t.order)

et al.

FOLLOWUP

If we don't want to modify the original SQL statement; if we execute this:

SELECT t.id
     , t.history
     , t.active
     , t.jsonorder
  INTO @id
     , @history
     , @active
     , @jsonorder
  FROM `myTable` t
 WHERE t.uid = ...

And then we want to perform an assignment to another user defined variable, based on a conditional test, we can achieve that in another SELECT or a SET statement.

For example:

SELECT @myNewVal := IF(@jsonorder IS NULL,'zzz','yyy') ;
                      ELSE 'yyy'

-or-

SELECT CASE
         WHEN @jsonorder IS NULL 
         THEN 'zzz'
         ELSE 'yyy'
       END
  INTO @myNewVal

-or-

SET @myNewVal := IF(@jsonorder IS NULL,'zzz','yyy')

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

This solve for cases where order is null, but not for when myUid doesnt exist in your table

SELECT id,history,active, COALESCE(order,1)
INTO @id,@history,@active,@order
FROM myTable 
WHERE uid = myUid

Upvotes: 0

Related Questions