MySQL Forums
Forum List  »  InnoDB

URGENT - Multiple Table Update Queries Reverting After 10 Mins
Posted by: Foggy City
Date: March 13, 2008 05:56AM

Hi

Windows XP Pro SP2 (All Updates) & MySQL 5.0.45 Community NT with InnoDB tables.

Can someone please help with the following: I am running 11 update queries which take a source table and then update the main contact tables in OS Commerce (customers table in one-to-many relationship with address_book table).

The 11 source tables hold the "cleansed" data subsets for the entire customers-address_book dataset.

I run through each of the queries (see below) and then use an access application to go row by row and field by field to compare the data. Everything seems fine after the update queries are run but when I go back after ten minutes (and running the other queries) the data seems to revert back to the original state - it's like a rollback has occurred.

In the example query below, GoldSmiths holds all the update data subset for the customers-address_book dataset.

START TRANSACTION;

UPDATE (Goldsmiths INNER JOIN address_book ON Goldsmiths.address_book_id = address_book.address_book_id)
INNER JOIN customers ON address_book.customers_id = customers.customers_id
SET address_book.site_id = Goldsmiths.site_id,
address_book.is_account_address = Goldsmiths.is_account_address,
address_book.entry_company = Goldsmiths.entry_company,
address_book.entry_company_tax_id = Goldsmiths.entry_company_tax_id,
address_book.entry_firstname = Goldsmiths.entry_firstname,
address_book.entry_lastname = Goldsmiths.entry_lastname,
address_book.entry_telephone = Goldsmiths.entry_telephone,
address_book.entry_fax = Goldsmiths.entry_fax,
address_book.entry_house = Goldsmiths.entry_house,
address_book.entry_street_address = Goldsmiths.entry_street_address,
address_book.entry_suburb = Goldsmiths.entry_suburb,
address_book.entry_postcode = Goldsmiths.entry_postcode,
address_book.entry_city = Goldsmiths.entry_city,
address_book.entry_state = Goldsmiths.entry_state,
address_book.entry_country_id = Goldsmiths.entry_country_id,
address_book.entry_zone_id = Goldsmiths.entry_zone_id,
address_book.note = Goldsmiths.note,
address_book.sage_contact_id = Goldsmiths.sage_contact_id,
customers.is_contractor = Goldsmiths.is_contractor,
customers.is_online_account = Goldsmiths.is_online_account,
customers.customers_firstname = Goldsmiths.customers_firstname,
customers.customers_lastname = Goldsmiths.customers_lastname,
customers.customers_email_address = Goldsmiths.customers_email_address,
customers.customers_default_address_id = Goldsmiths.customers_default_address_id,
customers.customers_telephone = Goldsmiths.customers_telephone,
customers.customers_fax = Goldsmiths.customers_fax,
customers.customers_password = Goldsmiths.customers_password,
customers.customers_newsletter = Goldsmiths.customers_newsletter,
customers.customers_group_id = Goldsmiths.customers_group_id,
customers.customers_group_ra = Goldsmiths.customers_group_ra,
customers.customers_payment_allowed = Goldsmiths.customers_payment_allowed,
customers.customers_shipment_allowed = Goldsmiths.customers_shipment_allowed,
customers.is_commercial_account = Goldsmiths.is_commercial_account,
customers.ref = Goldsmiths.ref And customers.customers_id=address_book.customers_id;

COMMIT;

Options: ReplyQuote


Subject
Views
Written By
Posted
URGENT - Multiple Table Update Queries Reverting After 10 Mins
3385
March 13, 2008 05:56AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.