MySQL server database query

toooki
toooki used Ask the Experts™
on
I have two database tables TAB1 TAB2 both in MYSQL server database.
Both tables have identical field names and data types as below:

F1  SMALLINT
F2  TIMESTAMP
F3  CHAR
F4  VARCHAR
F5  INT
F6  INT
F7  INT
F8  VARCHAR

I need to UPDATE all the values of F2 field (TIMESTAMP values) in TAB2 with the
correct values of F2 field in TAB1 (where rest of the record contents match in both tables).

There are NO primary keys in the tables.
TAB2 has ~800K records and TAB1 has ~900K records.
All the entries (with incorrect F1 field values) in TAB1
should ideally be in the TAB2 table.

What should be the best way to make this update?
Performance of the update query seems to be a big problem.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Aaron TomoskyDirector of Solutions Consulting

Commented:
Update tab2 b set f2 = (select f2 from tab1 a  where a.f1=b.f1 and a.f3=b.f3 and a.f4=b.f4 and a.f5=b.f5 and a.f6=b.f6 and a.f7=b.f7 and a.f8=b.f8)

Author

Commented:
Thank you. I tried similar query but it takes forever to run the query -- hour+ when I cancel it.
Actually I tried select query like this before:

select b.* from
tab1 a , tab2 b
where
a.f1=b.f1 and a.f3=b.f3 and a.f4=b.f4 and a.f5=b.f5 and a.f6=b.f6 and a.f7=b.f7 and a.f8=b.f8

Which gets stuck forever. Not sure what other option I have to do the same.
Aaron TomoskyDirector of Solutions Consulting

Commented:
1. Create an auto Inc pk
2. Create a unique index on the column group that should be unique.

Then yOu can do insert ignore and upsert and other fun things at super speed.

Why are there two tables anyway?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Aaron TomoskyDirector of Solutions Consulting

Commented:
More thoughts:
My way will be faster than your implied join as it updates each record after finding the match. Yours probably never even finished the join.
If there is a wry to limit the updates ( like if all the bad dates are from last month or something, it could go faster.

Author

Commented:
Thank you.

Actually TAB1 contents were merged into TAB2 on April 25, 2011. TAB2 has other entries that TAB1 does not have. When it was merged on April 25, 2011, the F2 field values were merged incorrectly onto TAB2. The incorrect values in  F2 field in TAB2 are mostly the sysdate vales (2010-04-24 04:20:29.0) or so in TAB2.

TAB1 table was created on April 25, 2011 as backup, which has the snapshot of the correct values of the source table TAB1 as of April 25, 2011.

Where do I create the auto Inc pk? (TAB1 or TAB2)
There is no primary keys in the table and the contents are such that there is no unique combination except for F1, F3, F4, F5, F6, F7 and F8 values combined.




Director of Solutions Consulting
Commented:
Update tab2 b set f2 = (select f2 from tab1 a  where a.f1=b.f1 and a.f3=b.f3 and a.f4=b.f4 and a.f5=b.f5 and a.f6=b.f6 and a.f7=b.f7 and a.f8=b.f8)
Where f2 between '2010-04-24' and '2010-04-25'

That way it will only update the ones with a date in that range.

Pk need to be in any table you intend to keep around.

Then make that the unique constraint.

Author

Commented:
Thank you. I am going to try that.

Author

Commented:
Could you please let me know if I will create the unique index on the "auto Inc pk" field or I will create the unique index on
F1  
F3  
F4  
F5  
F6  
F7  
F8  
The above 7 fields combined.?

Author

Commented:
I tried this query:
Update tab2 b set f2 = (select f2 from tab1 a  where a.f1=b.f1 and a.f3=b.f3 and a.f4=b.f4 and a.f5=b.f5 and a.f6=b.f6 and a.f7=b.f7 and a.f8=b.f8)
Where f2 between '2010-04-24' and '2010-04-25'

But it is run for two hour+  but the PC finally got frozen...
Aaron TomoskyDirector of Solutions Consulting

Commented:
Select * from tab2
Where f2 between '2010-04-24' and '2010-04-25'


Will show you how Manu rows still need updating. If this number is going down then it's working.

Author

Commented:
Thank you. I am still stuck. I followed your suggestions and made some changes in the approaches. I attached the steps in code.

I get stuck in the UPDATE query. (4th SQL in the list).

I made changes to the table names in it (the names I used in earlier posts do not apply now)..

Please see if you see any ways to improve the query. But I am somewhat stuck with the UPDATE now.

CREATE TABLE TEMP
(id MEDIUMINT NOT NULL AUTO_INCREMENT,
F1 smallint,
F2 timestamp,
F3 char,
F4 varchar(200),
F5 int,
F6 int,
F7 int,
F8 varchar(200),
PRIMARY KEY (id)
)

CREATE UNIQUE INDEX in_temp
ON TEMP (F1, F4, F5, F6, F7, F8);

insert TEMP (F1, F2, F3, F4, F5, F6, F7, F8)
select F1, F2, F3, F4, F5, F6, F7, F8
from MYTAB1 where F2 between '2011-04-15 06:06:20' and '2011-04-15 23:59:59'
(Source table MYTAB1 with BAD values of F2 . MYTAB1 has 900K records)
(I create a subset of the HUGE table MYTAB1 with the problem fields only. TEMP has 500K records)


(MYTAB2 has GOOD F2 field values)

update TEMP b 
set b.F2 = (select a.F2
from MYTAB2 a where 
a.F1 = b.F1
and a.F2 = b.F2 
and a.F4 = b.F4 
and a.F5 = b.F5 
and a.F6 = b.F6
and a.F7 = b.F7
and a.F8 = b.F8) 
(Updating 500K records)

DELETE from MYTAB1 where F2 between '2011-04-15 06:06:20' and '2011-04-15 23:59:59';
INSERT INTO MYTAB1 SELECT * FROM TEMP;

Open in new window

Author

Commented:
MYTAB2 has the same table structure as TEMP  with id primary key and the same UNIQUE key index.
 

Author

Commented:
OK. This is how I fixed it finally.

I copied the MySQL tables to an Oracle database (11gR2) via SQL developer IDE.
The Update queries took 2-minutes in Oracle.
Then I used the "MySQL toolkit" to migrate the updated Oracle table back to MySQL.

It may sound truly roundabout but it worked for me perfectly.

Thank you aarontomosky for your help but I guess nothing much I could do at the MySQL database side beyond what you suggested.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial