Creating a new table with info from two different tables?

Dev Shed Forums Sponsor:

January 18th, 2013, 01:15 PM

lexierowsell

Registered User

Join Date: Jan 2013

Posts: 6

Time spent in forums: 1 h 30 m 10 sec
Reputation Power: 0

Creating a new table with info from two different tables?

I know I am in over my head, but I am learning. Thanks for your patience in advance.

I have a table with this structure:

maxmag_kunena_messages

AND one with this structure:

maxmag_kunena_messages_text

That I want to combine to a table with THIS structure (id is a common column):

posts

January 18th, 2013, 01:32 PM

lexierowsell

Registered User

Join Date: Jan 2013

Posts: 6

Time spent in forums: 1 h 30 m 10 sec
Reputation Power: 0

I managed to get all of the info from maxmag_kunena_messages into posts using:

Code:

INSERT INTO posts (`id`, `parent`, `thread`, `catid`, `name`, `userid`, `email`, `subject`, `time`, `ip`, `topic_emoticon`, `locked`, `hold`, `ordering`, `hits`, `moved`, `modified_by`, `modified_time`, `modified_reason`) 
SELECT `id`, `parent`, `thread`, `catid`, `name`, `userid`, `email`, `subject`, `time`, `ip`, `topic_emoticon`, `locked`, `hold`, `ordering`, `hits`, `moved`, `modified_by`, `modified_time`, `modified_reason`     
	FROM `maxmag_kunena_messages`

However I can't seem to figure out how to add
message in maxmag_kunena_messages_text

to

message in posts

matching them based on id...

tried:

Code:

INSERT INTO posts (`message`) 
SELECT `message`     
	FROM `maxmag_kunena_messages_text`
        WHERE `maxmag_kunena_messages_text`.`id` = `maxmag_kunena_messages`.`id`

and

Code:

INSERT INTO posts (`message`) 
SELECT `message`     
	FROM `maxmag_kunena_messages_text`
        WHERE `maxmag_kunena_messages_text`.`id` = `posts`.`id`

but got error:

#1054 - Unknown column 'maxmag_kunena_messages.id' in 'where clause'

I am assuming I am missing something obvious, any one care to point out what exactly that is?

January 19th, 2013, 09:24 AM

f_razzoli

Contributing User

Join Date: Jan 2013

Location: Italy

Posts: 36

Time spent in forums: 4 h 37 m 48 sec
Reputation Power: 1

What you need is a simple JOIN. Something like:

Code:

CREATE TABLE `posts`
SELECT `mk`.`id` AS `id`, `mk`.`parent` AS `parent`, `mk`.`thread` AS `thread`, ... , `mkm`.`message` AS `message`
	FROM `maxmag_kunena` `mk` INNER JOIN `maxmag_kunena_messages` `mkm`
	ON `mk`.`id` = `mkm`.`id`;

(add the missing fields and check that the syntax is right, i didn't test it)

But maybe those table are separated for a reason (different storage engines?). Why don't you use a view, instead of merging the tables?

January 26th, 2013, 08:47 AM

lexierowsell

Registered User

Join Date: Jan 2013

Posts: 6

Time spent in forums: 1 h 30 m 10 sec
Reputation Power: 0

f_razzoli: Thanks for your response, I will run it and let you know if it works out. I am merging the tables because I am blundering my way through an attempt at a forum import from one platform to another. Thanks again.

Creating a new table with info from two different tables?

Developer Shed Advertisers and Affiliates