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. — user330315
– user330315, Commented Feb 13, 2019 at 15:11
@Jens so I've got two USINGs in my query, does that mean I just need to use more SELECT in the WHERE part? — A. L
– A. L, Commented Feb 13, 2019 at 15:15

A. L · Accepted Answer · 2019-02-14 06:46:02Z

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, 2019 at 6:46

A. L

12.7k29 gold badges100 silver badges179 bronze badges

answered Feb 13, 2019 at 16:19

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

A. L Over a year ago

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 Over a year ago

hold on, I messed up, that would just delete from item_instances, so the problem still stands

A. L Over a year ago

@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 · Accepted Answer · 2019-02-14 08:50:49Z

0

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

answered Feb 14, 2019 at 8:50

A. L

12.7k29 gold badges100 silver badges179 bronze badges

Collectives™ on Stack Overflow

Postgres - DELETE JOIN, USING giving error

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related