CODEWITHSUNDEEP
mysqlsqlmariadb
Luke Cousins
Luke Cousins

Reputation: 2156

MySQL/MariaDB - Overwrite field value in a SELECT query from within HAVING part of query

Is it possible to change the value of a field in a SELECT query, from within the HAVING part of the query? I don't want to touch the data in the database, this is just the values that come back in the select.

Here's a contrived example as the real query in question is very long and complicated.

SELECT t.col1, t.col2, (@all_is_ok = TRUE) as all_is_ok
FROM table t
WHERE t.col1 = 'something'
HAVING (
    (t.col2 = 1 AND t.col3 = 1)
    OR (t.col2 = 2 AND t.col3 = 2)
    OR (SET @all_is_ok = FALSE) /* If we get into this final OR in the HAVING 
                                   then I want the column all_is_ok to be set 
                                   to FALSE so that I still get the row back, 
                                   but can see that the row wasn't as expected */
)

We're using MariaDB 10.4.

I hope that makes sense and someone can help. Thank you.

Upvotes: 2

Views: 427

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

If you want to check that all values are 1/2 or 1/3, then use window functions:

SELECT t.col1, t.col2,
       (SUM( (t.col2 = 1 AND t.col3 = 1) OR (t.col2 = 2 AND t.col3 = 2) ) OVER () =
        COUNT(*) OVER ()
       )  as all_is_ok
FROM table t
WHERE t.col1 = 'something'

Upvotes: 2

Related Questions