Select Query to find a Range and Assign a new value

• nag.rampalli

  Grasshopper

  Points: 22

  More actions

  May 25, 2018 at 7:53 am

  #356103

  Hi Team

  i have below table with 3 columns ID, OPEN_CLOSE, Status. and blank values in status column.
  i need a query like, when there is an open status columns should be updated with started and when it finds close status columns should be udpated with Stopped.
  it is a scenario of power ON OFF on a particular day, how many times power on and OFF.

  ID	OPEN_cLOSE	Status
  1	Open	Started
  2	Closed	Stopped
  3	Open	Started
  4	Open
  5	Open
  6	Closed	Stopped
  7	Closed
  8	Closed
  9	Open	Started
  10	Open
  11	Open
  12	Closed	Stopped
  13	Closed

  Please help me in developing the script.

• Jack Corbett

  SSC Guru

  Points: 184393

  More actions

  May 25, 2018 at 8:00 am

  #1991971

  If I understand correctly I think this will work:

  
update status_table
     SET Status = CASE OPEN_CLOSE
                                     WHEN 'Open' THEN 'Started'
                                     WHEN 'Closed' THEN 'Stopped'
                                     ELSE NULL /* or empty string if the column doesn't allow null */
                              END
WHERE
       /* You don't really define how to know which rows to update so I'm guessing here */
      Status IS NULL; /* or Status = ''; If you don't allow NULLs */


  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question