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?


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

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 :slight_smile:

any insert/update/select that takes that long means you are doing something fundamentally wrong.

OMG! You’re very lucky your host hasn’t slapped you for that one. :eek:

do you have indexes on your table on the appropriate column(s)?

It eventually updated for me
I let it run all night
took it 15hrs in total

Thanks for your help

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

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.

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:


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?

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