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.
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
Last Comment
toooki
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)
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.
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.
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?
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?
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.
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.
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.
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
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.
ASKER
Thank you. I am going to try that.
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.?
F1
F3
F4
F5
F6
F7
F8
The above 7 fields combined.?
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...
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...
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.
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.
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.
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;
ASKER
MYTAB2 has the same table structure as TEMP with id primary key and the same UNIQUE key index.
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.
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.