SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    159
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Update thousands of records at once

    Hi
    Im using the below query to update a Date field in the "INVOICE" Table with the Date field in the "INVOICELIVE" Table

    when I run it its telling me its been executed but yet no records are been updated, then If i run it again sometimes I get a timeout error 1205

    Im trying to update almost 100,000 records would this maybe be causing the problem?

    Code:
    SELECT * FROM `test`.`invoice`
    
    UPDATE invoiceLIVE
    INNER
      JOIN invoice
        ON invoice.INVOICE_Number = invoiceLIVE.INVOICE_Number
       AND invoice.COMPANY_Number = invoiceLIVE.COMPANY_Number
    SET    invoiceLIVE.INVOICE_DateAdded = invoice.INVOICE_DateAdded

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by damo2009 View Post
    Im using the below query to update a Date field in the "INVOICE" Table with the Date field in the "INVOICELIVE" Table
    actually, your query is doing it the other way around, it updates invoicelive with data from invoice

    in any case, your syntax looks okay, maybe the tables are actually already in sync
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2006
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's likely that the large number of rows is a factor yes. But 100,000 is not an unmanagable amount of rows. How long does it take to run the select version of the query? I mean this:
    Code:
    SELECT
       *
    FROM
       invoiceLIVE    
    INNER
      JOIN invoice
        ON invoice.INVOICE_Number = invoiceLIVE.INVOICE_Number
       AND invoice.COMPANY_Number = invoiceLIVE.COMPANY_Number
    What results do you get for explain on the above query?

  4. #4
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    159
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    when I checked the records again, its actually close to 200,000 and thats in each table
    when I run that query it takes around 4mins then it shows me 1500 rows, but its still running away

  5. #5
    SitePoint Enthusiast
    Join Date
    Feb 2006
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by damo2009 View Post
    when I run that query it takes around 4mins then it shows me 1500 rows, but its still running away
    I guess that's your answer then, updating many of those rows would of course take more time. So maybe you'll have to run several queries and update only a fraction of the rows each time.

  6. #6
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    do you have indexes on your table on the appropriate column(s)?

  7. #7
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    159
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It eventually updated for me
    I let it run all night
    took it 15hrs in total

    Thanks for your help

  8. #8
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,144
    Mentioned
    190 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by damo2009 View Post
    It eventually updated for me
    I let it run all night
    took it 15hrs in total
    .....
    OMG! You're very lucky your host hasn't slapped you for that one.

  9. #9
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    any insert/update/select that takes that long means you are doing something fundamentally wrong.

  10. #10
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    159
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guelphdad View Post
    any insert/update/select that takes that long means you are doing something fundamentally wrong.
    I thought the same myself, because it was timing out and crashing on me before
    It was just pure chance it worked that time

    it was only a one off update to sort out the date, so i'll not be needing it again in a hurry


Bookmarks

Posting Permissions

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