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;