PostgreSQL使用inner join删除数据

14 浏览
0 Comments

PostgreSQL使用inner join删除数据

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

请问我的查询有什么问题吗？

admin 更改状态以发布 2023年5月23日
0

2 答案

0 Comments

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

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'

\n参考：https://www.drupal.org/docs/7/guidelines-for-sql/writing-code-compliant-with-both-mysql-and-postgresql/mysqlism-and-0

0
0 Comments 

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

0