0

I want to check if the logout_date column is updated. If it was updated then I have to modify the salary column.

login_time logout_time salary
2021-08-24 11:12:30 2021-08-24 14:34:56 1234

I have to check if the logout_time is updated, if it was updated then the salary would be 4567.

login_time logout_time salary
2021-08-24 11:12:30 2021-08-24 19:30:00 4567

If the logout_time is not updated, then the salary would be same i.e., 1234

login_time logout_time salary
2021-08-24 11:12:30 2021-08-24 14:34:56 1234

I have written below query, but i am not getting the required output.

select salary ,case when "logout_time" is updated then salary = new_salary from abc;

Can someone help me with the query Can someone please help me with the query.

Improve this question
6
  • Does that query run without an error? Commented Aug 24, 2021 at 13:07
  • How do you know if the logout_time column is updated? Commented Aug 24, 2021 at 13:08
  • Where does new_salary come from? Commented Aug 24, 2021 at 13:09
  • new_salary value will be retrieved from other table. There is seperate script which will be running to update logout_time Commented Aug 24, 2021 at 13:17
  • 1
    Looks like you need a trigger to handle this. Commented Aug 24, 2021 at 13:21

1 Answer 1

Reset to default
1

A column holds one value at a time, you cannot do what you want in a SQL statement (you can only say what is the logout time).

To work this out you need two values to compare. What you haven't said is how you are identifying an update. Is it any change after a point in time, is it each and every update, is it the first update after a particular point in time ...

The key here is you need to determine the logic to identify an update. Below are a number of options to then identify the changes.

Static Point In Time Comparision

If you are comparing against a static list (ie. you pick a point in time and any changes after that meet you criteria) then you could create a copy of the table with a CTAS statement and then run a query to join this snapshot table and the real one on some ID where the logout_time columns are different.

Trigger

If you want an event based update (ie. you want to know each and every time logout_time changes) then this can be accomplished with a trigger but we wary as triggers come with quite an overhead.

Flashback Query

Depending on your database setup and retention periods you might be able to run a flashback query to get older data. This isn't necessarily guaranteed so you would need to know what you are doing to use this.

SELECT * FROM myTable as of TIMESTAMP(sysdate - 0.5) would give you the contents of the table as of 12 hours ago which you could compare to the current value.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments