CODEWITHSUNDEEP
mysqlquery-optimizationsql-insertsql-delete
Sam
Sam

Reputation: 4487

Inserting & removing rows in a MySQL database without multiple queries

Check this example before reading the question - http://www.sqlfiddle.com/#!2/fcf3e/8

The following data comes from a form, the user simply removed a product from a special offer. 

Array(
    'special_offer_id' => 1,
    'product_ids' => Array(
        0 => 1,
        0 => 2
    )
)

Originally I wanted to use this query...

REPLACE INTO `foo` VALUES (1, 1), (2, 1);

But this won't remove the product that the user removed - only update the others.

So I'm forced to perform 2 queries...

DELETE FROM `foo` WHERE `special_offer_id` = 1;
INSERT INTO `foo` VALUES (1, 1), (2, 1);

Is there a better way to do this without having to perform 2 queries?

Example: http://www.sqlfiddle.com/#!2/fcf3e/8

Upvotes: 2

Views: 185

Answers (1)

GuZzie
GuZzie

Reputation: 974

I don't think it is possible within MySQL to combine DML statements. I do know that Oracle and MSSQL have the merge function for this but I think MySQL doens't have this function but i'm not quite sure about that.

Looking at your fiddle and what the code actually does I've came up with a different approach. If you loop through your array of data which is present and put the output into 1 variable and use the delete to delete the rows which do not match.

Here's an example based on your sqlfiddle (note that the array is not valid as it is not named correctly in the fiddle)

// Declare var and fill with array result
$exists = '';

for ($c = 0; $c < count($array); c++)
{
    if ($c == (count($array) -1))
    {
      $exists .= $array[$c]['product_ids'];
    }
    else
    {
      $exists .= $array[$c]['product_ids'].',';
    }
}

Then instead of doing two queries, you can do it with one

DELETE FROM `foo` WHERE `special_offer_id` NOT IN ('.$exists.');

Upvotes: 1

Related Questions