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

user_1178671 0
Viewed 80

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.

mariadb mysql sql
2 Answers

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'
user_11440350

I don't think this behaviour is achievable in the HAVING, you'd have to move it to the SELECT part of your query, for example

SELECT t.col1, t.col2, 
CASE
    WHEN t.col2 = 1 AND t.col3 = 1 THEN 'FINE'
    WHEN t.col2 = 2 AND t.col3 = 1 THEN 'FINE'
    ELSE 'NOT FINE'
END AS all_is_ok
FROM table t
WHERE t.col1 = 'something'

With either condition

AND all_is_ok = 'FINE'

or

HAVING all_is_okay = 'FINE'

I believe both would work, but untested.

user_15576170
Related