Highlighted

Complex query - Oracle 12

LEGEND ,
May 25, 2017

Copy link to clipboard

Copied

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,

^_^

Views

203

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Complex query - Oracle 12

LEGEND ,
May 25, 2017

Copy link to clipboard

Copied

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,

^_^

Views

204

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
May 25, 2017 0
BKBK LATEST
Adobe Community Professional ,
May 27, 2017

Copy link to clipboard

Copied

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

)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 27, 2017 0