CODEWITHSUNDEEP
sqlpostgresqljoinsql-delete
A. L
A. L

Reputation: 12649

Postgres - DELETE JOIN, USING giving error

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

Upvotes: 2

Views: 4076

Answers (2)

A. L
A. L

Reputation: 12649

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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;

Upvotes: 2

Related Questions