PostgreSQL 使用内部连接删除

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'

我收到以下错误 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 ...

我试着给

DELETE B from m_productprice B  INNER JOIN m_product C ON B....
 ERROR:  syntax error at or near "B"

我试着给

ERROR:  syntax error at or near "INNER"
LINE 1: DELETE from m_productprice B  INNER JOIN m_product C ON B.m_...

我的查询有什么问题？

原文由 dude 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

这对我有用：

 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');

原文由 dude 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

如果您有多个联接，则可以使用逗号分隔的 USING 语句：

 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'

参考

原文由 Mahesh 发布，翻译遵循 CC BY-SA 4.0 许可协议