Convert many updating queries to 1 quey

bono56 · #1 (**permalink**) 06-21-07, 08:37

i am workin on system that get some data from xml file & insert them into mysql database.
number of records in every xml files are over 200 & i cant INSERT & UPDATE them one by one.(because make the page operation very slow).
for insertin data i dont have problem, but for updating data i dont know how to handle the operation in all cases.
for example in case that updating field is similar i used this conversion:

Code:

UPDATE table1 SET del='Yes' WHERE id=101;
UPDATE table1 SET del='Yes' WHERE id=102;
UPDATE table1 SET del='Yes' WHERE id=103;
==>
UPDATE table1 SET del='Yes' WHERE id=101 OR id=102 OR id=103;

but in this case i dont have any idea:

Code:

UPDATE table1 SET serial_num='AB101' WHERE id=101;
UPDATE table1 SET serial_num='AB102' WHERE id=102;
UPDATE table1 SET serial_num='AB103' WHERE id=103;

how can convert these queries to 1 query:

mike_bike_kite · #2 (**permalink**) 06-21-07, 09:52

I don't know anything about XML but I'll start you off with a few ideas.

Is updating 200 rows really that slow - have you tested to see how long it takes? I tried something similar just now on my, very average shared host, and it was instant.

The updates could be sped up by testing that the value isn't already set correctly and having an index that covers both the id and the serial_num.

Code:

UPDATE table1 SET serial_num='AB101' WHERE id=101 and serial_num!='AB101';

Mike

aschk · #3 (**permalink**) 06-21-07, 10:00

Use Prepared statements

UPDATE table1 SET serial_num=? WHERE id=?

then execute every statement for each set of values...

gvee · #4 (**permalink**) 06-21-07, 10:31

If it really follows this logic

Code:

UPDATE table1 SET serial_num='AB101' WHERE id=101;
UPDATE table1 SET serial_num='AB102' WHERE id=102;
UPDATE table1 SET serial_num='AB103' WHERE id=103;

Then you might be able to get away with

Code:

UPDATE table1 SET serial_num = 'AB' + id

Internet Services

Web Development

Digital Marketing

Consumer Tech