CODEWITHSUNDEEP
sqlstored-procedures
Nanda kumar Baskar
Nanda kumar Baskar

Reputation: 43

How do I update yes/no values in if condition in SQL query?

I would like to have yes/no values in one of the columns based on the below query. I'm successful in getting yes value, but I got no idea how to have no value in the column.

create procedure xyz
as
update event_alerts 
set server_match = 'YES' 
where event_alerts.server_name in 
(
    select server_name 
    from event_alerts
    inner join sql_servers on
        event_alerts.server_name = sql_servers.List_of_servers
)

update event_alerts set server_match = 'No'

I'm stuck with the last line on how to update 'no' value in the column.

Upvotes: 0

Views: 2099

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

You can use a case expression and subquery:

update event_alerts
    set server_match = (case when event_alerts.server_name in 
                                      (select server_name
                                       from event_alerts ea join
                                            sql_servers ss
                                            on ea.server_name = ss.List_of_servers
                                      )
                             then 'yes' else 'no'
                        end);

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can do join with update :

update ea 
      set ea.server_match = (case when ss.server_name is null then 'No' else 'Yes' end)
from event_alerts ea left join
     sql_servers ss
     on ss.server_name = ea.server_name;

Note : JOINwith update syntax may very depend on database. But, you didn't tag the right DBMS that you are using..

Upvotes: 1

Related Questions