具有内部连接的 PostgreSQL delete

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_...

我的问题有什么问题吗？

209878 次浏览
小开 
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';

或

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');
小开
最佳答案

这招对我很管用:

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

另一种适用于Postgres 9.1+的形式是将公共表表达式与用于连接的USING语句结合起来。

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';
小开

只需使用INNER JOIN, LEFT JOIN或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'
)

为了优化查询，

小开

如果你有多个连接，你可以使用逗号分隔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'

Reference .

小开

基本上这里提到的所有东西都在文档中提到了，但没有人具体说明是什么。所以这就是当前的 DELETE docs, Notes部分所说的:

PostgreSQL允许你在WHERE条件中通过在USING子句中指定其他表来引用其他表的列。例如，要删除给定制作人制作的所有电影，可以这样做:

DELETE FROM films USING producers
WHERE producer_id = producers.id AND producers.name = 'foo';

这里实际上发生的是电影和制片人之间的连接，所有成功连接的电影行都被标记为删除。这个语法不是标准的。更标准的做法是:

DELETE FROM films
WHERE producer_id IN (SELECT id FROM producers WHERE name = 'foo');

在某些情况下，连接样式比子选择样式更容易编写或执行更快。


提交答案