Clever Geek Handbook

Remove row from table where column does not exist in another table

I have this SQL query that deletes user preferences from USERPREF table if not logged in for 30 days (last logon date is 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 USERPREF line is also removed in this 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
+3


source to share


4 answers


Modify the outer join, reverse the condition (so you match the users you want to keep) and move it to the join, then use IS NULL

to delete the unconnected rows:

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 the outer join returns all NULL values ​​when the join misses. By moving the date condition into a join, you can use it, but you don't require the concatenated string. The where clause filters out all rows that have data that you want to keep - leaving only the ones you want to delete.

+4


source


Not 100% I understand your question, but I think you can search left join

and check if MOMUSER.LOG IS NULL

(should be null if not actually joined to



DELETE USERPREF FROM USERPREF
LEFT JOIN MOMUSER ON MOMUSER.CODE = USERPREF.CUSER
WHERE MOMUSER.LOG_START < GETDATE()-30
OR MOMUSER.LOG_START IS NULL
0


source 


DELETE  FROM USERPREF u
 WHERE NOT EXISTS(SELECT NULL
                    FROM MOMUSER MOMUSER
                   WHERE MOMUSER.CODE = u.CUSER AND  MOMUSER.LOG_START < GETDATE()-30)
0


source


This is an extension to Seth's answer, but using the cte.Remeber clause and in the join, exclude the values ​​before joining

with cte
    as
    (
    select 
    *
    from USERPREF up
    left join
    MOMUSER  mu
    on 
    mu.CODE = up.CUSER
    and MOMUSER.LOG_START >= GETDATE()-30
    where MOMUSER.LOG_START IS NULL
    )
    ---select * from cte
    delete from cte
0


source






More articles:


All Articles