从mysql表中删除不匹配记录的最佳方法

I have a table with productId, color, size and other fields. I created following array from post values. I update the table rows with matching fields product_id, color_id, size_id. But I need to delete unmatched other 2 rows. I tried below query.

DELETE FROM fc_product_quantities WHERE product_id=18 AND color_id NOT IN (9,1) AND size_id NOT IN (23,24)

But it's not working as I expected and no records has been deleted. Is there any other way to delete unmatched records using mysql and php.

(
    [0] => Array
        (
            [pid] => 18
            [list] => 1
            [color] => 9
            [size] => 23
            [qty] => 10
            [price] => 522.22
            [offerprice] => 250.00
            [default] => 1
        )

    [1] => Array
        (
            [pid] => 18
            [list] => 1
            [color] => 1
            [size] => 24
            [qty] => 25
            [price] => 145.22
            [offerprice] => 222.22
            [default] => 0
        )
)

My table is

CREATE TABLE IF NOT EXISTS `fc_product_quantities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL DEFAULT '0',
  `color_id` int(11) NOT NULL DEFAULT '0',
  `size_id` int(11) NOT NULL DEFAULT '0',
  `quantity` int(11) NOT NULL DEFAULT '0',
  `qty_price` float(11,2) NOT NULL DEFAULT '0.00',
  `offer_price` float(11,2) NOT NULL,
  `default` tinyint(1) NOT NULL DEFAULT '0',
  `sold` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

INSERT INTO `fc_product_quantities` (`id`, `product_id`, `color_id`, `size_id`, `quantity`, `qty_price`, `offer_price`, `default`, `sold`) VALUES
(1, 18, 9, 23, 10, 522.22, 250.00, 1, 0),
(2, 18, 9, 22, 25, 245.55, 354.22, 0, 0),
(3, 18, 1, 22, 74, 444.44, 777.77, 0, 0),
(4, 18, 1, 24, 25, 145.22, 222.22, 0, 0);

PHP code

$totalSizesArr = array();
$totalColorsArr = array();
foreach ($quantities as $xKey => $xValue) {
    $pid = $xValue['pid'];
    $colorId = $xValue['color'];
    $sizeId = $xValue['size'];
    $qty = $xValue['qty'];
    $qPrice = $xValue['price'];
    $qOfferPrice = $xValue['offerprice'];
    $qDefault = $xValue['default'];

    $qtyDetails = $this->product_model->get_product_quantity($product_id,$colorId,$sizeId);
    if($qtyDetails->num_rows > 0) {
        $updateCondition = array(
            'product_id'=>$product_id,
            'color_id'=>$colorId,
            'size_id'=>$sizeId
            );
        $updateValues = array(
            'quantity'=>$qty,
            'qty_price'=>$qPrice,
            'offer_price'=>$qOfferPrice,
            'default'=>$qDefault
        );
        $this->product_model->update_details(PRODUCT_QUANTITIES,$updateValues,$updateCondition);
        echo $this->db->last_query();
        echo '</br>';
    } else {
        $uCondition = array();
        $uDatarr = array('product_id'=>$product_id,'color_id'=>$colorId,'size_id'=>$sizeId,'quantity'=>$qty,'qty_price'=>$qPrice,'offer_price'=>$qOfferPrice,'default'=>$qDefault);
        $this->product_model->commonInsertUpdate(PRODUCT_QUANTITIES,'insert',$iuExcludeArr,$uDatarr,$uCondition);
        echo $this->db->last_query();
        echo '</br>';
    }
    $totalSizesArr[] = $sizeId;
    $totalColorsArr[] = $colorId;
}
$totalSizesStr = implode(',', $totalSizesArr);
$totalColorsStr = implode(',', $totalColorsArr);
$this->product_model->quantityDelete(PRODUCT_QUANTITIES,$product_id,$totalColorsStr,$totalSizesStr);

I want to delete row with id 2 and 3. These 2 are not matching.

I know one solution is to delete all rows for product_id 18 and insert the new rows. But is there any other way without deleting all records and insert new rows?

Sorry for my bad english

I'm not sure, but you might want to look at your logic. Don't you mean to do:

DELETE FROM fc_product_quantities WHERE product_id=18 AND (color_id NOT IN (9,1) OR size_id NOT IN (23,24))

This is just a guess, because all the AND's might leave you with nothing to delete.

Your current query exclude to much rows from delete.

DELETE
FROM fc_product_quantities
WHERE product_id=18
AND color_id NOT IN (9,1)
AND size_id NOT IN (23,24)

Example with a row like this :

Array
(
  [pid] => 18
  [color] => 9
  [size] => 50
)

This row will not be deleted because you exclude value 9 in color_id. This will work :

DELETE
FROM fc_product_quantities
WHERE product_id = 18
AND (
    (color_id != 9 AND size_id != 23)
    OR
    (color_id != 1 AND size_id != 24)
)