How to UPDATE table using calculated column from subquery in Sybase

Question

I have a table that I want to update that contains a column called 'expiration_days'. What I am doing is trying to update the records in the 'expiration_days' column by using an 'alias column' (not sure what to call it) that is apart of a subquery where I calculated the number of days until a user's password has expired. The column from the subquery that I want to take the values from and update them in the actual table is called 'countdown'. I named the subquery results 'query' (derived table). So far I have this:

UPDATE LOGIN_INFO
SET expiration_days = query.countdown
FROM (
select li.name as name, countdown = 365 - datediff(day, sl.pwdate, getdate())
from master..syslogins sl, LOGIN_INFO li
where li.name = sl.name) query
WHERE LOGIN_INFO.name = query.name

The issue I am having is I get this error: You cannot use a derived table in the FROM clause of an UPDATE or DELETE statement. ( I also get: Incorrect syntax near ')' on the subquery where clause)

Is there a way I can take the results from the calculated column in a select statement and update the column in the LOGIN_INFO table in one query or some other easy clean way?

Answer 1

Perhaps something along the lines of:

update login_info
set    expiration_days = (select 365 - datediff(day,s1.pwdate,getdate())
                          from   master..syslogins s1
                          where  s1.name = li.name)
from   login_info li
where  exists(select 1
              from   master..syslogins s2
              where  s2.name = li.name)

NOTES:

• the exists() clause is added to insure we don't erroneously update a row in login_info that doesn't have a match in syslogins, otherwise OP will need to modify the logic accordingly (ie, what to set expiration_days to if a matching rows does not exist in syslogins?)
• if syslogins.pwdate is NULLable (I don't have access to a running ASE instance at the moment) then OP will need additional logic to handle the scenario where s1.pwdate is NULL; default countdown to some hardcoded value? or perhaps modify the exists() to include the additional clause and s2.pwdate is not NULL?