Copying data from one table to another?

[madmatter23]

I'd really appreciate help with this sql issue. It may seem a little complicated at first glance, but it's not that bad. Ok, here it goes.

I'm redesigning a site for a host that has 2 radio shows. Each radio show has 3 tables to itself. One table for the show info, one for guest info, and one table that stores info about which guest was on which show (by coordinating the show's primary key value with the guest's).

It's kind of a stupid way of doing things. The show table should really just have a column containing the guest pk. It's unnecessary to have the third table. Oh well. Here's a very condensed representation of the tables:


Show1:

-tblShow
--pk_tblShow
--topic

-tblGuest
--pk_tblGuest
--name

-trelCoord
--pk_trelCoord
--pk_tblShow
--pk_tblGuest

Show2:

-mpShow
--pk_mpShow
--topic

-mpGuest
--pk_mpGuest
--name

-trelRelate
--pk_trelRelate
--pk_mpShow
--pk_mpGuest



Here's the challenge. I'm trying to combine the 2 shows (6 tables) into the same database. Ideally I would just combine the 2 show tables and the 2 guest tables (they have identical structures), but then there's no way to know which guest was on which show. And I can't combine the 2 tertiary tables, their autoinc values overlap.

If anyone knows how I can just take the the whole trelRelate.pk_tblGuest column and add it to tblShow where trelRelate.pk_tblShow = tblShow.pk_tblShow, I would really appreciate help with the query.

THANK YOU!

[madmatter23]

UPDATE.

Problem halfway solved. I moved everything from show2 to show1 using this:

insert into tblShow (topic, showdate, starttime, endtime, lastmod, moduser, category, audio, video, shownumber, fk_tblGuest) select mpShow.topic, mpShow.showdate, mpShow.starttime, mpShow.endtime, mpShow.lastmod, mpShow.moduser, mpShow.category, mpShow.audio, mpShow.video, mpShow.shownumber, trelRelate.pk_tblGuest from mpShow, trelRelate where mpShow.pk_mpShow = trelRelate.pk_mpShow


Now I just need to update tblShow so that fk_tblGuest is populated with data from trelCoord.pk_tblGuest where trelCoord.pk_tblShow = tblShow.pk_tblShow.
I'm trying this but it's not working:

UPDATE tblShow SET fk_tblGuest = (SELECT trelCoord.pk_tblGuest FROM trelCoord WHERE trelCoord.pk_tblShow = tblShow.pk_tblShow) WHERE EXISTS (SELECT trelCoord.pk_tblGuest FROM trelCoord WHERE trelCoord.pk_tblShow = tblShow.pk_tblShow)


can anyone tell me what the problem is with this second query? I keep getting this error:
"#1242 - Subquery returns more than 1 row "

Help?
Thanks.

[che_anj]

INSERT new_table SELECT * FROM old_table;

[madmatter23]

Hm. Just doing a second insert actually causes a problem. It inserts new rows rather than updating current ones. I'll keep working on it, thanks.