CODEWITHSUNDEEP
mysqlsql
Code_Ed_Student
Code_Ed_Student

Reputation: 1190

Expiring a value based on a date field

I have a table called course offerings. In this table you will find each course with its respective course_start_date and course_end_date. I am also using an enum field called course_status. There are two possible status for each course IN SESSION or Closed but not both. How could I create a query that will change the status to Closed if it is passed its course_end_date? SQLFIDDLE 

CREATE TABLE course_offerings 
    (
     id int auto_increment primary key, 
     course_name varchar(20), 
     course_start_date date,
     course_end_date date,
     course_status enum('CLOSED','IN SESSION')
    );

INSERT INTO course_offerings
(course_name, course_start_date, course_end_date, course_status)
VALUES
('Math', '2013-02-20', '2014-02-20', 'IN SESSION'),
('Science', '2013-02-20', '2014-04-18', 'IN SESSION');

Upvotes: 0

Views: 26

Answers (3)

JDGuide
JDGuide

Reputation: 6525

Try this:-

update course_offerings set course_status='Closed' where course_end_date<str_to_date('2014-02-02','%Y-%m-%d');

Upvotes: 0

Nikhil Talreja
Nikhil Talreja

Reputation: 2774

I don't know if there is a dynamic way to do what you are trying.

This simple SQL query can help you update such records:

UPDATE course_offerings 
SET course_status = 'Closed' 
WHERE course_end_date < SYSDATE

Upvotes: 1

Sadikhasan
Sadikhasan

Reputation: 18600

Try this

UPDATE course_offerings 
SET course_status = 'Closed' 
WHERE course_end_date = "passed_date";

Upvotes: 0

Related Questions