inserting value into a column of a sql table by a function

i have a table which has 5 columns:ID,staff name,course name,issued-date,expire-date
which indicates staff courses,that have an issue date and an expire date(the course certificate will be expired in that date).
i want to compare my records of 'expire-date' to current date and if the course date is expired,insert "EXP" in front of that record into a new column, otherwise insert "OK" in front of that record into that new column.i want to apply it to all my records.
i don't know to use function or query or procedure!
i will be thankful if someone helps.

2 Answers
2

You shouldn't persist that, as the time will constantly change and therefore more and more certificates become expired. But that changes won't be reflected in your data. Check it when you retrieve the data. Use a CASE for that.

CASE

SELECT CASE
WHEN "expire-date" <= now() THEN
'EXP'
ELSE
'OK'
END,
*
FROM "elbat";


(As you didn't tag your DBMS, I don't know which function/variable can be used to get the current time. Replace now() with the right one.)

now()

you can use case statement;

Insert into <table>
(
id,
staff_name,
course_name,
issued_date,
expired_date,
status --new column with values 'OK' / 'EXP'
)
select
id,
staff_name,
course_name,
issued_date,
expired_date,
case when sysdate>expired_date then 'OK' else 'EXP' end
from
<table>;


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.