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!

 

[planty]

Have you tried using the ChannelID field in your JOIN rather than the ChannelName? If the ChannelName is not unique that might be causing the duplicate key issue.

 

[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.

 

[Gornall]

You're not truly using a tempTable there are you? eg a # table.

Does the Channeltemp have the primary key on there? If not, it might be that your trying to add record sets multiple times. Which is fine on the temp (without a key) but soon as you insert where there is a constraint then you hit the issue.

 

[Gornall]

Also why the need for the GROUP By clause? You're not aggregating anything?

 

[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

 

[Gornall]

I take it you aren't using T-SQL? TempTables always have a # e.g. in your case it would be #CHANNELTEMP. Otherwise you've create a 'real' table in the schema.

Insert the data into the temporary table and select * from it before the insert. See what is actually in there.


I'm also assuming the main table does have primary keys, which is what is throwing up the error. I can only assume somewhere there is a duplicate ID.

Try [ Select T.CHANNELID, count(T.CHANNELNAME) from ChannelTemp group By T.ChannelID ] after the insert to Temptable

If there are any >1 then that's your issue.

 

[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

 

[Gornall]

Ah no worries, that's what confused me a little

Any luck on the other questions, e.g. seeing whats in temp before appending?

 

[Inferno99]

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

WHERE C.CHANNELNAME IS NULL
AND T.CHANNELID NOT IN (SELECT CHANNELID FROM CHANNELS)

Something along the lines of the above.

 

[Kotu]

What about using INSERT IGNORE? It would return a warning rather than stopping and returning an error.