Creating custom sql code to update qty of a certain attribute not found in csv file during import

Question

Im currently working on a sql formula that when i import a particular supplier and products are removed from the file to only update the qty of those in the database to 0. But what happens is that it updates every other supplier as well. Here is the formula below. We have created an attribute in magento called supplier. So how can we add this in the formula to only update products not on file that are "supplier A" as 0 only? Thanks

 /* This SQL block may be executed after product import and it will set products that are not in imported file as "Qty 0". */
/* Please note that this SQL block must be modified manually according to your database specifics to work properly.              */ 

UPDATE /*PREFIX*/cataloginventory_stock_item SET qty = 0 
  WHERE product_id in (&`prod.ProdNotInCsv`);/*DELIMITER*/

UPDATE /*PREFIX*/cataloginventory_stock_status SET stock_status = 0 
  WHERE product_id in (&`prod.ProdNotInCsv`);/*DELIMITER*/

Answer 1

I know this doesn't answer you sql question but have you considered using Magento objects to do this instead?

For example, you could load all of the products that weren't imported (ProdNotInCsv) into an array and update all of the products that aren't in Supplier A.

This is untested but is the general idea of what I'm referring to above.

<?php

require("app/Mage.php");
Mage::app();

// You could load your product list of non-imported items
// here list this then explode them into an array

$imported_products = "sku1,sku2,sku3,sku4"; // list of skus here, separated by comma. These could be pulled in externally or passed as an argument to the php
$prod_not_in_csv = explode(',', $imported_products);

$product_collection = Mage::getResourceModel('catalog/product_collection')
    ->addAttributeToSelect('entity_id')
    ->addAttributeToFilter('sku', array('in' => $prod_not_in_csv));
    ->addAttributeToFilter('supplier', array('eq' => 'Supplier A'));
    ->load();

foreach ($product_collection as $product) {
   $product_id = $product->getId();       
   $product_stock = Mage::getModel('cataloginventory/stock_item');
   $product_stock->assignProduct($product_id)
                 ->setData('is_in_stock', 0);
                 ->setData('qty', 0);
   $product->setStockItem($product_stock);
}