SQL Join question

[PaulStat]

I have a temporary table that I use to populate all of my other tables, naturally the temporary table has everything in it. So to insert to another table I have inserts like this

INSERT INTO CHANNELS(CHANNELID,CHANNELNAME)
SELECT
   T.CHANNELID, T.CHANNELNAME
FROM
   CHANNELTEMP T
   LEFT JOIN CHANNELS C ON C.CHANNELNAME=T.CHANNELNAME
WHERE C.CHANNELNAME IS NULL
GROUP BY T.CHANNELNAME

Which works great when channels is empty. Sometimes however I get an error which says something along the lines of 'Duplicate channelid '12345' exists for primary key'

So it sounds like I'm trying to insert a channelid which already exists in channels.

Essentially I want to insert anything from channeltemp that isn't in channels.

In an attempt to refresh my knowledge on joins I found this diagram.

To me this indicated that I needed the same SQL as above but a right join instead. This however doesn't give me any inserts into the channels table!

 

[PaulStat]

hmmm yeah I really don't know why I was using channelname instead of channelid (revisting old code from 2 years ago), in theory there shouldn't be duplicate channelsnames either! I'll give it a go though thanks.

 

[PaulStat]

Again both good points, I can't recall any reason to groupby, yes it is a proper temporary table and no the temporary table doesn't have any primary keys, it does however have an index on the channelname

 

[PaulStat]

Nope I'm using MySQL

CREATE TEMPORARY TABLE CHANNELTEMP (
   CHANNELID INT NOT NULL, CHANNELNAME VARCHAR(100), INDEX(CHANNELNAME))

CREATE TABLE IF NOT EXISTS CHANNELS (
   CHANNELID INT NOT NULL, CHANNELNAME VARCHAR(100), INDEX(CHANNELNAME), PRIMARY KEY(CHANNELID)
                                  ) ENGINE=INNODB