MySQL INSERT... ON DUPLICATE KEY etc... Please Help!

[discostudio]

So...

 

Here's what I have so far...

 

CREATE TABLE `Retailers` (
  `retailerId` int(20) NOT NULL auto_increment,
  `password` varchar(255) NOT NULL,
  `retailerName` varchar(255) NOT NULL,
  `retailerUrl` varchar(255) NOT NULL,
  `retailerEmail` varchar(150) NOT NULL,
  `retailerTelephone` varchar(80) NOT NULL,
  `displayDetails` tinyint(1) NOT NULL default '1',
  `businessName` varchar(200) NOT NULL,
  `retailerContactName` varchar(150) NOT NULL,
  `retailerContactTelephone` varchar(80) NOT NULL,
  `retailerContactEmail` varchar(150) default NULL,
  `mailerLisingViewed` tinyint(1) NOT NULL default '0',
  `mailerLinkOut` tinyint(1) NOT NULL default '0',
  `mailerWeeklyStats` tinyint(1) NOT NULL default '1',
  `mailerBilling` tinyint(1) NOT NULL default '1',
  `address1` varchar(200) NOT NULL,
  `address2` varchar(200) NOT NULL,
  `address3` varchar(200) NOT NULL,
  `address4` varchar(200) NOT NULL,
  `address5` varchar(60) NOT NULL,
  `countryId` int(11) NOT NULL default '0',
  `languageId` int(11) NOT NULL default '0',
  `currencyId` int(11) NOT NULL default '0',
  `googleBaseId` int(11) NOT NULL default '0',
  `useGoogleFeed` tinyint(1) NOT NULL default '0',
  `googleChoiceAuth` tinyint(1) NOT NULL default '0',
  `packageType` tinyint(1) NOT NULL default '0',
  `packageExpiryDate` date NOT NULL default '0000-00-00',
  `authorised` tinyint(1) NOT NULL default '0',
  `active` tinyint(1) NOT NULL default '0',
  `dateAdded` datetime NOT NULL,
  `lastLoginDate` date NOT NULL default '0000-00-00',
  `lastUpdate` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`retailerId`,`googleBaseId`),
  KEY `countryId` (`countryId`),
  KEY `languageId` (`languageId`),
  KEY `currencyId` (`currencyId`),
  KEY `googleBaseId` (`googleBaseId`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

 

QUERY:

 

"INSERT INTO ".$glob['table_retailers']." 
(`googleBaseId`, `retailerName`, `retailerContactEmail`, `retailerEmail`,
`countryId`, `languageId`, `currencyId`,
`useGoogleFeed`, `dateAdded`, `lastUpdate`)
SELECT 
DISTINCT `gb`.`retailerId` AS `googleBaseId`, 
`gb`.`retailer` AS `retailerName`,
`gb`.`retailerEmail` AS `retailerContactEmail`,
`gb`.`retailerEmail` AS `retailerEmail`,
`co`.`countryId` AS `countryId`,
`l`.`languageId` AS `languageId`,
`cu`.`currencyId` AS `currencyId`,
'1',
NOW(),
NOW()
FROM ".$glob['table_google_base_results']." AS `gb` 
INNER JOIN ".$glob['table_countries']." AS `co` ON (`gb`.`country` = `co`.`iso`)
INNER JOIN ".$glob['table_languages']." AS `l` ON (`gb`.`language` = `l`.`languageCode`)
INNER JOIN ".$glob['table_currencies']." AS `cu` ON (`gb`.`currency` = `cu`.`currencyCode`)
ON DUPLICATE KEY UPDATE
`lastUpdate` = NOW()
"

 

So, the two fields I am relying upon are `retailerId` and `googleBaseId`...

 

So here's what I want to work...

 

If I add the following:

 

|------------|--------------|
| retailerId | googleBaseId |
|------------|--------------|
| AUTO       | 2323         | // INSERTS ROW WITH retailerId 1
| AUTO       | 2324         | // INSERTS ROW WITH retailerId 2
| AUTO       | 0            | // INSERTS ROW WITH retailerId 3
| AUTO       | 0            | // INSERTS ROW WITH retailerId 4
| AUTO       | 2323         | // THIS SHOULD NOT INSERT AT ALL, BUT SIMPLY UPDATE `lastUpdate` = NOW()
| 1          | 2323         | // THIS SHOULD NOT INSERT AT ALL, BUT SIMPLY UPDATE `lastUpdate` = NOW()
| AUTO       | 2323         | // OK, So why does this insert a NEW row with the googlebaseId as '2323'
|------------|--------------| // when we already have a row with googleBaseId '2323'

 

If I am not being specific enough, please let me know.

 

So what I want is to only insert a row when, if I am trying to insert a `retailerId` value or a `googlebaseId` value that doesn't exist seperately

[discostudio]

So... will anyone help me?

[discostudio]

Guys, this is my first use of this forum and the lack of a detailed reply is making me start to loose faith in the forum.

 

Please, will someone help me out?

 

Thanks

[RichardRotterdam]

so basically you want to do an insert if the entry doesnt exist.

why not just do a select before you insert anything

[discostudio]

Thanks for your reply.

 

OK, that's sounds great, are you saying that I can select to see if a row exists and if not, INSERT?

 

- So how would I do that, I see no reference in the manual.

[discostudio]

Oh, by the way, I know how to do this in PHP programically;

 

How do I do it without php, in a single query?