Postgres - DELETE JOIN, USING giving error

Question

So it doesn't seem like there are DELETE JOINs in Postgres so I tried using USING:

DELETE FROM
    creator.list_items li
USING
    creator.lists l
USING
    item_instances ii
WHERE
    li.list_id = l._id
    AND
    li.item_instance_id = ii._id
    AND
    ii.item_id IN ($1:list)
    AND
    l._id = $2
    AND
    l.account_id = $3

but it just gives me the error:

ERROR:  syntax error at or near "USING"
LINE 5:                 USING

my original query:

DELETE 
    li
FROM
    creator.list_items li
JOIN
    creator.lists l
    ON
        li.list_id = l._id
JOIN
    item_instances ii
    ON
        li.item_instance_id = ii._id
WHERE
    ii.item_id IN ($1:list)
    AND
    l._id = $2
    AND
    l.account_id = $3

The `using` keyword can only be used once, just like e.g. the `where` keyword. — , Feb 13 '19 at 15:11
Possible duplicate of [PostgreSQL delete with inner join](https://stackoverflow.com/questions/11753904/postgresql-delete-with-inner-join) — Jens, Feb 13 '19 at 15:13
@Jens so I've got two `USING`s in my query, does that mean I just need to use more `SELECT` in the `WHERE` part? — A. L, Feb 13 '19 at 15:15

score 2 · Answer 1 · edited Feb 14 '19 at 06:46

2

You can actually use JOIN in the USING clause:

DELETE FROM
    creator.list_items li
USING creator.lists l JOIN
      item_instances ii
      ON li.list_id = l._id
WHERE li.item_instance_id = ii._id AND
      ii.item_id IN ($1:list) AND
      l._id = $2 AND
      l.account_id = $3;

edited Feb 14 '19 at 06:46

A. L

11,695
23
85
163

answered Feb 13 '19 at 16:19

Gordon Linoff

1,242,037
58
646
786

for that query I'm getting: `HINT: There is an entry for table "li", but it cannot be referenced from this part of the query.` needed to switch `ii` and `li` to get rid of the error. – A. L Feb 14 '19 at 03:45
hold on, I messed up, that would just delete from `item_instances`, so the problem still stands – A. L Feb 14 '19 at 06:46
@a_horse_with_no_name i made an edit before which was incorrect, which I am referring to, you can check the edit history. – A. L Feb 14 '19 at 08:49

score 0 · Answer 2 · answered Feb 14 '19 at 08:50

My functioning query:

DELETE FROM
    creator.list_items li
USING 
    creator.lists l 
WHERE 
    li.item_instance_id 
        IN 
        (
            SELECT
                _id
            FROM
                creator.item_instances
            WHERE
                item_id IN ($1:list) 
        )
    AND
    li.list_id = $2 
    AND
    li.list_id = l._id
    AND
    l.account_id = $3

Postgres - DELETE JOIN, USING giving error

2 Answers2