Multiple Insert, Update or Delete in one Query (php-mysql).

Many times we want to make a multiple Insert (I), Update (U) or Delete (D) on a certain database table. The first thought of doing it would be to fire Insert, Update or Delete query inside a for(each) loop. But this is not the best solution regarding the system performance. Ever thought of inserting Or Updating |or Deleting multiple records in 1 query? Yes it is possible. It may look complex to beginners. The idea is to create a query dynamically for all the values you want to Insert, Update or Delete. The example of each is given below. I am not going to write the code for creating query, but will show how the final query looks like (the syntax) after dynamically created.

Imagine a table name employees with fields id (int auto increment) age (int), name (varchar) and sex (char).

The syntax of the Multiple Insert Query for the employees table would be –

$sql = "INSERT INTO employees (age, name, sex)
VALUES
(25, 'Swanand', 'M'),

(26, 'Smeeta', 'F'),

(33, 'Vicky', 'M'),

(42, 'Tony', 'M')”;

One another alternative I know for inserting multiple records is through LOAD DATA INFILE statement of mysql. This approach gives the best performance as compared to multiple insert. Its description and syntax is out of the scope of this article. You can refer in mysql manual for more details.

The syntax of the Multiple Update Query for the employees table would be –

$sql = “UPDATE employees

SET age =

CASE id

WHEN $id1 THEN $age1

WHEN $id2 THEN $age2

WHEN $id3 THEN $age3

WHEN $id4 THEN $age4

END

, name =

CASE id

WHEN $id1 THEN '$name1'

WHEN $id2 THEN '$name2'

WHEN $id3 THEN '$name3'

WHEN $id4 THEN '$name4'

END

, sex =

CASE id

WHEN $id1 THEN '$sex1'

WHEN $id2 THEN '$sex 2'

WHEN $id3 THEN '$sex3'

WHEN $id4 THEN '$sex4'

END

WHERE id in ($id1,$id2,$id3,$id4)”;

The syntax of the Multiple Delete Query for the employees table would be –

$sql = “DELETE FROM employees WHERE id in ($id1,$id2,$id3,$id4)”;