[SOLVED] Duplicate information from one table to another, a better way?

[Platinumweaver]

MYSQL: 5.0.67

Level of Confidence: LOW

 

two tables share data

 

posts contains post_id, topic_id, forum_id

topics contains topic_id, forum_id

 

My problem is that the process I used to convert the data from one forum software to another has backfired and left posts.forum_id blank (default value - 0).

 

I can manually go through and edit every value of posts.forum_id to match up with the appropriate topics.forum_id because posts.topic_id and topics.topic_id are the same, but I am hoping that someone can save me the hours of manual work with a short mysql query that can look up topics.forum_id for each instance of posts.topic_id and write the correct values back into posts.forum_id

 

Any help would be greatly appreciated,

 

Thanks,

PW.

[Mchl]

UPDATE posts AS p, topics AS t SET p.forum_id = t.forum_id WHERE p.topic_id = t.topic_id

 

(untested)

[Platinumweaver]

Tested.

Worked.

Confirmed.

 

Fantastic work, thank you so much, you just saved me 70,000 entries worth of work.

[Mchl]

you just saved me 70,000 entries worth of work.

 

That feels good

 

Cheers!

 

P.S. There's 'Mark as Solved' button somewhere below. Click it.