Jump to content

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


discostudio

Recommended Posts

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.