CODEWITHSUNDEEP
sqlpostgresqlsubquerysql-delete
Savir
Savir

Reputation: 18418

Delete with subquery that produces two columns (in Postgresql)

I have to delete a few records that match two columns calculated with a subquery.

I can properly see them with this query:

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;

This works, and I see the 7 assignments I want to delete.

However, deleting is not as easy as changing the SELECT by DELETE...

If I do:

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

I get: 

ERROR:  syntax error at or near ","

I've tried quite an assortment of combinations, yet I can't get it to work. I imagine it must be quite simple, but I'm quite a newbie in SQL.

Basically, I have a subquery that properly produces two columns that I can use for a SELECT FROM user_assignments and now I want to DELETE FROM user_assignments the records that I know I can SELECT.

Any hints would be very appreciated. Thank you in advance.

Upvotes: 6

Views: 4366

Answers (2)

Savir
Savir

Reputation: 18418

Oh, I got it (I think).

Kuddos to this tutorial this tutorial and particularly, the section SQL delete records using subqueries with alias.

If someone else is interested, what I did was:

DELETE FROM user_assignments ua
WHERE EXISTS(
    SELECT user_assignments.user_id as uid,
           user_assignments.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
        AND ua.user_id = uid
        AND ua.job_selection = jid
)

This query also works fine with SELECT * FROM user_assignments

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Use in or 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
             );

Upvotes: 7

Related Questions