Complex query - Oracle 12

Report · May 25, 2017

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,

^_^

Report · May 27, 2017

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

)