I have this SQL query that deletes a user's preferences from USERPREF table if they have not logged in for 30 days (last login date located in MOMUSER table), however, it does not verify that the user still exists in MOMUSER. How can I change this so that if USERPREF.CUSER does not exist in MOMUSER.CODE that the USERPREF row is also deleted in that situation since they will not have a last login date?
DELETE USERPREF FROM USERPREF
INNER JOIN MOMUSER ON MOMUSER.CODE = USERPREF.CUSER
WHERE MOMUSER.LOG_START < GETDATE()-30
Change to an outer join, reverse the condition (so you match users you want to keep) and move it into the join, then use IS NULL to delete rows without joins:
DELETE USERPREF
FROM USERPREF
LEFT JOIN MOMUSER ON MOMUSER.CODE = USERPREF.CUSER
AND MOMUSER.LOG_START >= GETDATE()-30
WHERE MOMUSER.LOG_START IS NULL
Recall that an outer join returns all nulls when the join misses. By moving the date condition into the join, you get to exercise it but not require a joined row. The where clause filters out all rows that have the kind of data you want to keep - leaving only those you want to delete.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments