Avatar of toooki
toooki
 asked on

MySQL server database query

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.
MySQL ServerDatabases

Avatar of undefined
Last Comment
toooki

8/22/2022 - Mon
Aaron Tomosky

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)
toooki

ASKER
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 Tomosky

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?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Aaron Tomosky

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.
toooki

ASKER
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.




ASKER CERTIFIED SOLUTION
Aaron Tomosky

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
toooki

ASKER
Thank you. I am going to try that.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
toooki

ASKER
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.?
toooki

ASKER
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 Tomosky

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
toooki

ASKER
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

toooki

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

ASKER
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.