Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MYSQL CASE THEN statement with multiple values

Tags:

mysql

I am trying go select multiple values with CASE statement. I noticed we cannot do

CASE 
    WHEN wall.type="bk" 
    THEN books.id1,books.id2, // and so on
END as column_1,

Is there a way to do THEN with multiple columns or do we need to simply write a bunch of CASE THEN statements? that seems messy

like image 491
Semur Nabiev Avatar asked Apr 14 '12 17:04

Semur Nabiev


People also ask

Can CASE statement return multiple columns?

No you can't do that. A case expression is used to help decide the value for a column. Also you don't need subselects inside like that.

Can we use case in update statement in MySQL?

#3) With UPDATE StatementsMySQL CASE can also be used while updating an existing column in the table. Let's try to understand this with the help of an example with the test data we have. We can use the below query to achieve such updates without having to write UPDATE queries to have multiple WHERE or IF clauses.

Can we use CASE statement in WHERE clause in MySQL?

Another way to use the Case Statement is within the WHERE clause. There, it may be utilized to alter the data fetched by a query based on a condition. Within that context, the Case Statement is ideally suited to both static queries, as well as dynamic ones, such as those that you would find inside a stored procedure.

Can we use case in MySQL?

CASE() Function in MySQL. CASE() function in MySQL is used to find a value by passing over conditions whenever any condition satisfies the given statement otherwise it returns the statement in an else part.


1 Answers

No, it is just a single value. Additionally, it is contradictory to use "multiple columns" and name those multiple columns as column_1, right? :)

You can use another column to store the other id with (a similar case) and use nulls to represent the else values, just like you're doing now.

Example:

CASE 
    WHEN wall.type="bk" 
    THEN books.id1
END as column_1,
CASE 
    WHEN wall.type="bk" 
    THEN books.id2
END as column_2

Check the official documentation for more information.

like image 178
Mosty Mostacho Avatar answered Oct 15 '22 12:10

Mosty Mostacho