CODEWITHSUNDEEP
mysqlcoalesce
Kburgen9
Kburgen9

Reputation: 25

MySQL: Select with Coalesce still returning null

My understanding of COALESCE is that it returns the first non null value. I’m using COALESCE in the select statement below, but it’s still returning null. How can I fix it or is there a better way?

SET NEW.random = ( SELECT coalesce(animals) FROM list ORDER BY RAND() LIMIT 1 );

DB Fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=aca780246b24ec4cce8d9978c1113d26

Upvotes: 0

Views: 343

Answers (2)

Md. Suman Kabir
Md. Suman Kabir

Reputation: 5453

Seems you have mis-understanding with the COALESCE function! You can use multiple columns as parameter while using this function like COALESCE(col1, col2, col3). It returns the first non null value in case of using more than one column as parameter. To understand, please see the SQL Here

More detail here

According to your comment, I think you are looking for this :

SELECT animals from list where animals is not null ORDER BY RAND() LIMIT 1

Upvotes: 1

forpas
forpas

Reputation: 164089

You don't need COALESCE().
Just filter out the NULLs from the table list:

SET NEW.random = ( SELECT animals
                   FROM list
                   WHERE animals IS NOT NULL
                   ORDER BY RAND() LIMIT 1 );

See the result in your fiddle.

Upvotes: 0

Related Questions