从内部连接mysql查询中删除 - 两个表的多个条件

I have two MySQL tables:

我有两个MySQL表:

Table AllProducts  
AccountID, ProductOwner, ProductNumber  
100001, Tom, ABC1  
100001, Tom, ABC2  
100001, Greg, ABC3  
100002, Charlie, ABC2  

Table ProductData  
AccountID, ProductNumber, ProductDesc  
100001, ABC1, DescHere  
100001, ABC2, DescHere  
100001, ABC3, DescHere  
100002, ABC2, DescHere

I need to delete everything from ProductData where ProductNumbers are the same in both tables and I will specify with Variables what the AccountID is, and also who the ProductOwner is.

我需要删除ProductData中的所有内容,其中ProductNumbers在两个表中都是相同的,我将使用变量指定AccountID是什么,以及ProductOwner是谁。

E.g I know AccountID is 100001 and ProductOwner is Tom. Therefore I want rows 1 and 2 in ProductData table to be deleted only.

例如,我知道AccountID是100001而ProductOwner是Tom。因此,我希望仅删除ProductData表中的第1行和第2行。

Edit: I believe I may have just cracked query I've been working on

编辑:我相信我可能刚刚破解了我一直在努力的查询

mysql_query("DELETE ProductData.* FROM ProductData  
  INNER JOIN AllProducts ON ProductData.ProductNumber = AllProducts.ProductNumber  
    WHERE (ProductData.AccountID = '100001'  
      AND AllProducts.ProductOwner = 'TOM')");

I've done a quick test and it seems to work - any thoughts/criticisms?

我做了一个快速测试,它似乎工作 - 任何想法/批评?

2 个解决方案

#1

Your use of mysql_query is deprecated as of PHP 5.5.0 and will be removed in the future. You should start using MySQLi or the PDO_MySQL extension.

从PHP 5.5.0开始,不推荐使用mysql_query,将来会删除它。您应该开始使用MySQLi或PDO_MySQL扩展。

I would further suggest you store your query:

我建议你存储你的查询:

  DELETE ProductData.* FROM ProductData  
  INNER JOIN AllProducts ON ProductData.ProductNumber = AllProducts.ProductNumber  
    WHERE (ProductData.AccountID = '100001'  
      AND AllProducts.ProductOwner = 'TOM'

In a stored procedure on the database.

在数据库上的存储过程中。

When using PDO's for example, you can call it as follows:

例如,当使用PDO时,您可以按如下方式调用它:

    $db = new PDO('mysql:host=xxx;port=xxx;dbname=xxx', 'xxx', 'xxx', array( PDO::ATTR_PERSISTENT => false));

    // Be sure to cleanse the passed in arguments!
    $stmt = $db->prepare("CALL deleteProductData($accountId, $productOwner)");

    // call the stored procedure
    $stmt->execute();

Stored procedure example:

存储过程示例:

CREATE DEFINER=`root`@`localhost` PROCEDURE `deleteProductData`(IN `accountId` BIGINT, IN `productOwner` VARCHAR(128))
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
    DELETE FROM ProductData  
        INNER JOIN AllProducts ON ProductData.ProductNumber = AllProducts.ProductNumber  
        WHERE ProductData.AccountID = accountId AND AllProducts.ProductOwner = productOwner;
END

This way you are moving all MySQL code out of php and into the database, where it belongs.

这样你就可以将所有MySQL代码移出php并进入它所属的数据库。

#2

DELETE FROM ProductData 
      WHERE ProductNumber IN 
            (SELECT ProductNumber
               FROM AllProducts
              WHERE AccountId=100001
                AND ProductOwner='Tom')

#1