MySQL: Where clause in Trigger, Column which is not null = column or is null

I need help with a where clause to return values where co1 which is not null = col2 or is null.

id | col1 | col2 | result
1  |   a  |      |  True
2  |   b  |   b  |  True
3  |      |   c  |  False
4  |      |      |  False

id1 evaluates to true because col1 is not null and col2 is null. id2 evaluates to true because col1 = col2. id3 evaluates to false because col1 is null. id4 evaluates to false because col1 is still null.

This Fiddle may help explain more of what I’m trying to accomplish if the above is unclear.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=287c91f43c17e4b48925412b8821cec5

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

null is not a value, it is the lack thereof, and always returns null when compared using the = operator. So if col1 = col2, it means that they are both not null, and are equal. In short, you could query this with two simple conditions with an or logical operator:

SELECT *
FROM   mytable
WHERE  col1 = col2 OR (col1 IS NOT NULL AND col2 IS NULL)

Method 2

I need help with a where clause to return values where co1 which is not null = col2 or is null.

This is describing:

where col1 = col2 or col1 is null

However, you might want a NULL-safe comparison. That would treat NULL = NULL. If so, MySQL offers one:

where col1 <=> col2

If you are trying to calculate the result column, that would most succinctly be expressed as:

where col1 = coalesce(col2, col1)

I would describe this as “col1 = col2 or col2 is null and col1 is not null”.

Method 3

I think you are searching for such a update Query

CREATE TRIGGER trigger_01
    AFTER UPDATE
    ON Tab2
    FOR EACH ROW
    BEGIN
    UPDATE Tab3
    SET results  = ( SELECT results FROM Tab1 
 where IF(NEW.user_col1 is not NULL OR NEW.user_col1 = '',IF( Tab1.col1 = NEW.user_col1, 1,0),1)
 and IF(NEW.user_col2 is not NULL OR NEW.user_col2 = '',IF( Tab1.col2 = NEW.user_col2, 1,0),1)
 and IF(NEW.user_col3 is not NULL OR NEW.user_col3 = '',IF( Tab1.col3 = NEW.user_col3, 1,0),1)
 AND IF(NEW.user_col4 is not NULL OR NEW.user_col4 = '',IF( Tab1.col4= NEW.user_col4, 1,0),1)
    ORDER BY RAND() LIMIT 1);
    end;
UPDATE Tab2
Set user_col1 = 'a'
Where user_id = 1;

UPDATE Tab2
Set user_col2 = ''
Where user_id = 1;

UPDATE Tab2
Set user_col3 = ''
Where user_id = 1;

UPDATE Tab2
Set user_col4 = 'd'
Where user_id = 1;

SELECT * FROM Tab2 where user_id =1;
SELECT * FROM Tab3 where user_id = 1;
user_id | user_col1 | user_col2 | user_col3 | user_col4
------: | :-------- | :-------- | :-------- | :--------
      1 | a         |           |           | d        

user_id | results
------: | :------
      1 | Audi   

db<>fiddle here


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x