CODEWITHSUNDEEP
sqloracle-databasesql-update
Rob
Rob

Reputation: 117

SQL to update an Oracle table based on another table

I have two tables in an Oracle database: USERS and USERLOG

Key field: USERS.IDCOL = USERLOG.USERID

Removed users are deleted from the USERS table; therefore there will be some IDs in the USERLOG that won't match to the USERS table.

I want to do two things to the user list:

  1. Lock any users who haven't accessed system in last 9 months (i.e. their ID doesn't appear in the USERLOG in the last 9 months, which I guess will use something like:

    months_between( SYSDATE , USERLOG.LOGIN ) <= 9
  2. Lock any users that have only one entry in the USERLOG, as long as it isn't within the last month/30 days (i.e. when the account was created)

To lock a user I need to update the USERS.ISLOCKED column from 0 to 1

Many thanks

Upvotes: 1

Views: 257

Answers (2)

Rajesh Chamarthi
Rajesh Chamarthi

Reputation: 18808

months_between( SYSDATE , USERLOG.LOGIN ) <= 9

1) In this case, the userlog.login isn't from a single row, but the max login time among a series of logins. There are different ways to do this, but I think the below query captures the requirement more clearly. "All users where no login exists in the last 9 months"

select *
from   users usr
where  not exists (
    select 1
    from   userlog log
    where  usr.user_id  = log.user_id
      and  log.login    > add_months(sysdate,-9)
)

Check to make sure you are getting the users you are, looking for and convert this select to a delete.

2) This would be 2 different conditions to satisfy the two rules..

select *
from   users
where  not exists (
    select 1
    from   userlog
    where users.userid = userlog.userid
      and userlog.login > add_months(sysdate,-1)
) AND (
     1 = (select count(*)
     from   userlog
     where  users.userid = userlog.userid)
    )

Upvotes: 1

haki
haki

Reputation: 9759

Couldent test it but the first one 

update users a 
set islocked = 1 
where exists (
    select  1
    from    userlog b
    where   a.idcol = b.userid
    group  by b.userid
    having  months_betwen(sysdte , max(userlog)) <= 9)

the second one, if i got it right, you want to lock users that have only one entrance from over 30 days ago .... if so then 

update users set islocked = 1 
where idcol in (
    select  userid
    from    userlog
    group by userid
    having count(*) = 1
    and count(case when month_between(login , sysdate) < 1 then 1 else null end) = 0)

so i'm basically counting the entrances in the last month and overall. syntax might be off, i cant run an example here.

Upvotes: 1

Related Questions