Update parent table id from old table that has relation

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.
LVL 2
bignadadAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bignadadAuthor Commented:
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
Aaron TomoskySD-WAN SimplifiedCommented:
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
bignadadAuthor Commented:
i have index on results_class activity and class unique.

What might that look like?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Aaron TomoskySD-WAN SimplifiedCommented:
Instead of inner join use left join
0
bignadadAuthor Commented:
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
Aaron TomoskySD-WAN SimplifiedCommented:
are you truncating the table first?
0
bignadadAuthor Commented:
The tmp table I'm inserting to?  Yes. It's empty
0
Aaron TomoskySD-WAN SimplifiedCommented:
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
bignadadAuthor Commented:
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
Tomas Helgi JohannssonCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.