asked on

Update parent table id from old table that has relation

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.

Addie Baker

ASKER

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

Aaron Tomosky

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.

Addie Baker

ASKER

i have index on results_class activity and class unique.

What might that look like?

Aaron Tomosky

Instead of inner join use left join

Addie Baker

ASKER

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

Aaron Tomosky

are you truncating the table first?

Addie Baker

ASKER

The tmp table I'm inserting to? Yes. It's empty

Aaron Tomosky

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.

Addie Baker

ASKER

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

ASKER CERTIFIED SOLUTION