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.
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.
After you delete dt_activity, you may then do the following:
SELECT count(DISTINCT USER_UUID) AS numberOfUsers
Delete users from first table:
DELETE FROM web-user-info
WHERE USER_UUID NOT IN
SELECT DISTINCT USER_UUID