CODEWITHSUNDEEP
sql
Nick
Nick

Reputation: 1243

Combine two SQL statements, set a status

I currently have two SQL statements that I'd like to combine into one, and set a status if it meets each one. I've posted some massively stripped down code below:

select * from database where date1 < SYSDATE
select * from database where date2 < SYSDATE

Ideally, I'd like it so if it meets the criteria in the first statement, it'd set a flag of 'status1' and if it doesn't meet that criteria, but meets the second statement's criteria I'd like to set a flag of 'status2' - hope that makes sense!

For example the data would be like: Name | ID | Status

Thanks :)

Upvotes: 0

Views: 254

Answers (4)

Ben Hoffstein
Ben Hoffstein

Reputation: 103375

Since you said you wanted to UPDATE the status...

UPDATE database 
SET flag = CASE WHEN date1 < SYSDATE THEN 'status1' WHEN date2 < SYSDATE THEN 'status2' ELSE NULL END

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425713

UPDATE  mytable
SET     flag =
        CASE
        WHEN  date1 < SYSDATE THEN
                'status1'
        ELSE
                'status2'
        END
WHERE   date1 < SYSDATE
        OR
        date2 < SYSDATE

Upvotes: 1

user330315
user330315

Reputation: 

select * 
from ( 
  select name, id, 'status1' as status
  from some_table 
  where date1 < SYSDATE

  UNION ALL

  select name, id, 'status2'
  from some_table 
  where date2 < SYSDATE
)

Upvotes: 1

Fosco
Fosco

Reputation: 38526

Give this a shot:

select Name, ID, CASE 
     WHEN date1 < SYSDATE THEN 1 
     WHEN date2 < SYSDATE THEN 2
END as Status
from mytable
where date1 < SYSDATE or date2 < SYSDATE

Upvotes: 1

Related Questions