从表中删除另一个表中不存在列的行

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?

我有这个SQL查询，如果他们没有登录30天（上次登录日期位于MOMUSER表中），则会从USERPREF表中删除用户的首选项，但是，它不会验证用户是否仍然存在于MOMUSER中。如何更改此设置，以便如果MOMUSER.CODE中不存在USERPREF.CUSER，那么USERPREF行也会在该情况下被删除，因为它们没有上次登录日期？

    DELETE USERPREF FROM USERPREF
    INNER JOIN MOMUSER ON MOMUSER.CODE = USERPREF.CUSER
    WHERE MOMUSER.LOG_START < GETDATE()-30

4 个解决方案

#1

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:

更改为外部联接，反转条件（以便匹配要保留的用户）并将其移动到联接中，然后使用IS NULL删除没有联接的行：

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.

回想一下，当连接未命中时，外连接返回所有空值。通过将日期条件移动到连接中，您可以练习它但不需要连接的行。 where子句过滤掉所有具有您想要保留的数据的行 - 只留下您想要删除的行。

#2

Not 100% I understand your question but I think that you might be looking for a left join and check if MOMUSER.LOG IS NULL (should be null if it didn't actually join

不是100％我理解你的问题，但我认为你可能正在寻找左连接并检查MOMUSER.LOG是否为NULL（如果它实际上没有加入则应为null

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

#3

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

#4

This is an extension to Seth Answer but using cte.Remeber and clause in join eliminates the values before joining

这是Seth Answer的扩展，但在连接中使用cte.Remeber和子句会在加入之前消除这些值

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

#1