CODEWITHSUNDEEP
phpmysqljoinsql-delete
Penindini
Penindini

Reputation: 13

Delete from inner join mysql query - two tables multiple conditions

I have two MySQL tables:

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.

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.

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?

Upvotes: 1

Views: 5286

Answers (2)

ManuelH
ManuelH

Reputation: 866

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.

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:

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

Upvotes: 1

mzedeler
mzedeler

Reputation: 4369

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

Upvotes: 0

Related Questions