sql - 使用产生两列的子查询删除(在 Postgresql 中)

Question

我必须删除一些与使用子查询计算的两列匹配的记录。

我可以通过这个查询正确地看到它们:

select * from user_assignments as ua, 
    (
        select user_assignments.user_id as uid, 
               job_selection as jid 
        from user_assignments
            join job_selections on job_id = jobs.id
            join data on job_selections.data_id = data.id
            where data.my_column IS NULL
    ) as sq
    where sq.uid = ua.user_id AND ua.job_selection_id = sq.jid;

这有效，我看到了我要删除的 7 个作业。

但是，删除并不像通过DELETE改变SELECT那么简单...

如果我这样做:

delete from user_assignments as ua, 
    (
        ...
    ) as sq
    where sq.uid = ua.user_id AND sq.jid = ua.job_selection_id;

我得到:

ERROR:  syntax error at or near ","

我已经尝试了很多种组合，但我无法让它发挥作用。我想它一定很简单，但我是 SQL 的新手。

基本上，我有一个子查询可以正确生成两列，我可以将它们用于 SELECT FROM user_assignments，现在我想 DELETE FROM user_assignments 我知道的记录我可以选择。

如有任何提示，我们将不胜感激。提前谢谢你。

Answer 1

使用 in 或 exists:

delete from user_assignments ua
where exists (select 1
              from user_assignments ua2 join
                   job_selections js
                   on ua2.job_id = js.id join
                   data d
                   on js.data_id = d.id
              where d.my_column IS NULL and
                    ua.user_id = sq.uid and ua.job_selection_id = sq.jid
             );