PostgreSQL delete with inner join

Question

DELETE B    FROM   m productprice B          INNER JOIN m product C ON B m product id   C m product id  WHERE  C upc    7094  AND B m pricelist version id    1000020    i am getting the following error PostgreSQL 8 2 11  ERROR   syntax error at or near  B  LINE 1  DELETE B   from m productprice B  INNER JOIN m product C ON       i tried giving   DELETE B from m productprice B  INNER JOIN m product C ON B      ERROR   syntax error at or near  B    i tried giving  ERROR   syntax error at or near  INNER  LINE 1  DELETE from m productprice B  INNER JOIN m product C ON B m       what is the problem with my query

User · Answer

Another form that works with Postgres 9 1  is combining a Common Table Expression with the USING statement for the join   WITH prod AS  select m product id  upc from m product where upc  7094   DELETE FROM m productprice B USING prod C WHERE B m product id   C m product id  AND B m pricelist version id    1000020

User · Answer

Just use a subquery with INNER JOIN  LEFT JOIN or smth else   DELETE FROM m productprice WHERE m product id IN     SELECT B m product id   FROM   m productprice  B     INNER JOIN m product C      ON   B m product id   C m product id   WHERE  C upc    7094     AND    B m pricelist version id    1000020      to optimize the query     use NOT EXISTS instead of IN and WITH for large subqueries

User · Answer

DELETE  FROM m productprice B        USING m product C  WHERE B m product id   C m product id AND       C upc    7094  AND                        B m pricelist version id  1000020     or  DELETE  FROM m productprice WHERE m pricelist version id  1000020  AND        m product id IN  SELECT m product id                         FROM m product                         WHERE upc    7094

User · Answer

If you have more than one join you could use comma separated USING statements   DELETE  FROM        AAA AS a  USING        BBB AS b        CCC AS c WHERE        a id   b id    AND a id   c id   AND a uid   12345    AND c gid    s434sd4    Reference

User · Answer

This worked for me   DELETE from m productprice WHERE  m pricelist version id  1000020         AND m product id IN  SELECT m product id                             FROM   m product                             WHERE  upc    7094

[sql] PostgreSQL delete with inner join

Examples related to sql

Examples related to postgresql