1 Reply Latest reply on May 27, 2017 3:15 AM by BKBK

    Complex query - Oracle 12

    WolfShade Level 4

      Hello, all,

       

      I'm not really sure if this can be accomplished.  But I'm going to give it a try, and I could really use some help with a complex query involving two tables (many-to-one, relational).

       

      First, a little background.  Oracle 12 database.  Chained commands are disabled.  Stored Procedures are not allowed.  (Not my choice; these are upper level decisions, above my paygrade.)  This is a very weak metrics-ish project.  Basically do a very, VERY lame job of "fingerprinting" a client browser for tracking purposes (but not the nefarious tracking you might be thinking of - it's to see in what order which links of one of our sites is being utilized, believe it or not to improve UI/UX.)

       

      The reason I am bringing this question here?  Because I am OCD when it comes to keeping databases as small as can be, and I want to run a scheduled task once a month that will prune the data - under specific conditions, just about anything older than 6 months.

       

      TABLES:

      web-user-info (table one)     web-user-activity (table two)
      USER_UUID (pk)                ACTIVITY_UUID (pk)
      USER_ID                       USER_UUID (fk)
      DT_CREATED                    DT_ACTIVITY
      FQDN                          URL
      IP_MACH_NAME                  QUERY_STRING
      

       

      So, what I'm aiming to do for this scheduled task is to delete rows from second table where DT_ACTIVITY is greater than 6 months from now.  If there are any rows still existing that are tied to a user in the first table, fine, as far as that user is concerned, move on to the next user.

       

      HOWEVER, if all records in table two for a user in table one are deleted, then delete the user from table one, as that user record is no longer needed.

       

      Now the delete portion of the SQL I can do with no problem (haven't written it, yet.. working on that).  What I'm at a loss for is checking the number of related records left, and deleting from table one.

       

      Any related thoughts appreciated.

       

      V/r,

       

      ^_^

        • 1. Re: Complex query - Oracle 12
          BKBK Adobe Community Professional & MVP

          After you delete dt_activity, you may then do the following:

           

          Users left:

          SELECT count(DISTINCT USER_UUID) AS numberOfUsers

          FROM web-user-activity

           

          Delete users from first table:

          DELETE FROM web-user-info

          WHERE USER_UUID NOT IN

          (

              SELECT DISTINCT USER_UUID

              FROM web-user-activity

          )