Solved

Update parent table id from old table that has relation

Posted on 2014-09-04
10
362 Views
Last Modified: 2014-09-15
Untitled-picture.png
I am creating new tables in my database because im trying to normalize them.

I am having to update these new tables with my old data.

i have populated tbl_activity and tbl_class from results_class table

I have also populated tbl_activity_class with all the class id's from tbl_class.

Now i need to update tbl_activity_class with my activity_id's from tbl_activity.

results_class has the right relationships between activity and class

How can i update tbl_activity_class activity_id from tbl_activity activity_id where results_class activity and class match class_id of tbl_class.

hope i explained this well enough.
0
Comment
Question by:bignadad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 2

Author Comment

by:bignadad
ID: 40306138
I came up with this to get the data into a tmp table so i could easily get it into the table needed.

However its taking forever to execute. Is there a quick way to run this query?

INSERT INTO tbl_activity_copy (activity_id, activity,class_id,class)
SELECT
  tbl_activity.activity_id,
  tbl_activity.activity,
  tbl_class.class_id,
  tbl_class.class
FROM tbl_class
  INNER JOIN results_class
    ON tbl_class.class = results_class.class
  INNER JOIN tbl_activity
    ON results_class.activity = tbl_activity.activity

Open in new window

0
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 40306346
looks pretty basic, do you have indexes on those columns you are joining on?

Try doing left joins for this insert, then delete rows with null you don't want after. that might go faster.
0
 
LVL 2

Author Comment

by:bignadad
ID: 40306432
i have index on results_class activity and class unique.

What might that look like?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 40306570
Instead of inner join use left join
0
 
LVL 2

Author Comment

by:bignadad
ID: 40306743
I tried left and it takes longer to run select query.

With either inner or left when I add insert the query takes forever then times out.  What are my options?
I'm using dbforge
0
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 40306835
are you truncating the table first?
0
 
LVL 2

Author Comment

by:bignadad
ID: 40306840
The tmp table I'm inserting to?  Yes. It's empty
0
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 40306850
I'm out of ideas, it's a simple query, not much to optimize. I suppose it could be running out of memory or something.
0
 
LVL 2

Author Comment

by:bignadad
ID: 40306856
Hmm.  Im not sure what's going on. The tables are both over 100k rows.

I'll post more details and sample data when I get to pc
0
 
LVL 25

Accepted Solution

by:
Tomas Helgi Johannsson earned 500 total points
ID: 40308419
Hi!

Are the columns  activity_id in tbl_activity and class_id in tbl_class auto_increment fields ?
If not you should consider that. Note the tbl_activity_class should not have auto_increment fields.

Then populating the tables would be as follows
insert into tbl_activity (activity) select distinct activity from results_class ;
commit;
insert into tbl_class (class ) select distinct class from results_class;
commit;
insert into tbl_activityclass (activity_id, class_id ) 
   select a.activity_id, c.class_id 
   from tbl_activity a, tbl_class c, results_class r
   where a.activity = r.activity
   and c.class = r.class;
commit;

Open in new window


Make sure that you have the following indexes

index on activity_id on tbl_activity
index on class_id on tbl_class
and
index on (activity_id, class_id ) on tbl_activity_class
index on (class_id, activity_id ) on tbl_activity_class

Those indexes will speed up selects on your tables.

Regards,
      Tomas Helgi
0

Featured Post

Upcoming Webinar: Securing your MySQL/MariaDB data

Join Percona’s Chief Evangelist, Colin Charles as he presents Securing your MySQL®/MariaDB® data on Tuesday, July 11, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question