Results 1 to 4 of 4
  1. #1
    Join Date
    May 2004
    Posts
    144

    Unanswered: Convert many updating queries to 1 quey

    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:

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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

  3. #3
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Use Prepared statements

    UPDATE table1 SET serial_num=? WHERE id=?

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

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •