Here's another one... Mysql UPDATE by SELECT

iceomnia · Apr 16, 2008

OK, so here's what I need to do...

...and beofre anyone says "Read the da*n manual" - MySQL doesn;t reference this whatsoever and if they do I cant find it.

I have my Old products table with the following columns:

productId
name
descr
price
available

...and another table with the following columns:

sku
title
description
price
stock

How do I do the following:

UPDATE `table2` with data SELECTed FROM `table1`

Please help!

iceomnia · Apr 16, 2008

So I've found the following syntax:

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...

Except this becomes a problem:

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. See Section 12.2.4, “INSERT Syntax”.

Is there a was to make mySQL UPDATE the row as or add a new one if one with a Primary key doesn;t exist?

NogDog · Apr 16, 2008

I think you can do:

UPDATE table_1 AS t1, table_2 AS t2
  SET
    t1.name = t2.title,
    t1.descr = t2.description,
    t1.price = t2.price,
    t1.available = t2.stock
  WHERE t1.productId = t2.sku

I'm making some assumptions here about how the tables are related, so caveat emptor.

Weedpacket · Apr 17, 2008

iceomnia wrote:
MySQL doesn;t reference this whatsoever and if they do I cant find it.

That doesn't prove anything: you haven't found the database forum either. Moving you there now.

iceomnia · Apr 17, 2008

NogDog wrote:
I think you can do:
UPDATE table_1 AS t1, table_2 AS t2
  SET
    t1.name = t2.title,
    t1.descr = t2.description,
    t1.price = t2.price,
    t1.available = t2.stock
  WHERE t1.productId = t2.sku
I'm making some assumptions here about how the tables are related, so caveat emptor.

Yes but that will only UPDATE the rows, not INSERT one if the primary key doesn't already exist.

Any other ideas?

iceomnia · Apr 17, 2008

INSERT INTO radiotronics.items 
(`sku`, `supplierId`, `supplierCode`, `barcode`, `manId`, `mpn`, `name`, `title`, 
`desc`, `html`, `bullets`, `stock`, `duedate`, `taxRateId`, 
`cost`, `markup`, `dateAdded`, `dimensions`, `weight`, `colour`)

SELECT `upn`, `supplier_id`, `supp_code`, `barcode`, `man_id`, `order_code`, `prod_name`, 
`prod_title`, `prod_tag`, `prod_desc`, `bullets`, `in_stock`, `date_available`, `tax_rate`, 
`trade_price`, `markup`, `date_added`, CONCAT_WS('x', `height`,`width`,`depth`), `weight`, `colour`
FROM discostu_discostu.Product_Prices

ON DUPLICATE KEY UPDATE sku=upn

Okay so this works, but it only INSERTS if the row doesn't exist, it doesn;t update the existing rows that already exist. Why is this?

NogDog · Apr 18, 2008

Maybe try an update, and if it returns an error or a mysql_affected_rows() returns 0, then do an insert? (Not very clean/efficient, I know, but all I can think of at this time -- maybe a stored procedure could help with the efficiency?)

iceomnia · Apr 28, 2008

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

iceomnia · May 1, 2008

So... Can anyone help?

Roger_Ramjet · May 2, 2008

You can either

INSERT ... ON DUPLICATE KEY UPDATE

or

REPLACE

Either one will do what you want , depending upon your conditions.

iceomnia · May 5, 2008

Roger Ramjet wrote:
You can either

INSERT ... ON DUPLICATE KEY UPDATE

or

REPLACE

Either one will do what you want , depending upon your conditions.

No disrespect, but you obviously did not read my opening post.

I am already doing the "INSERT ... ON DUPLICATE KEY UPDATE" method, however it's not working.

I cannot use REPLACE, as it completely deletes the row before REPLACing it.

Sxooter · May 6, 2008

iceomnia wrote:
No disrespect, but you obviously did not read my opening post.

I am already doing the "INSERT ... ON DUPLICATE KEY UPDATE" method, however it's not working.

No disrespect, but your original post says nothing about using insert on duplicate key or using replace. It is about updating one table with data from another table. Which may or may not need the "upsert" functionality you are now mentioning.

In fact, your second post comes across as a non-sequiter because the first post was answered by the response you got from NogDog about the update with a join.

So, it's not surprising that a response you got seems a bit off from what this thread has evolved into.

NogDog again answered your question when he suggested doing one then the other if the first failed. You can either update and check for affected rows = 0 then insert, which has a small race condition in that someone else might insert a row between your update and insert and your insert would fail. So, if you use that method you need to check for a failed insert and then run another update.

Or you can insert first, check for failure, then update if there is a row there already. There is no race condition caused failures here, but the method may not be what you want.

Think of which is the better way to "fail" from your apps perspective and chose.

iceomnia · May 6, 2008

Sxooter wrote:
No disrespect, but your original post says nothing about using insert on duplicate key or using replace. It is about updating one table with data from another table. Which may or may not need the "upsert" functionality you are now mentioning.

In fact, your second post comes across as a non-sequiter because the first post was answered by the response you got from NogDog about the update with a join.

So, it's not surprising that a response you got seems a bit off from what this thread has evolved into.

NogDog again answered your question when he suggested doing one then the other if the first failed. You can either update and check for affected rows = 0 then insert, which has a small race condition in that someone else might insert a row between your update and insert and your insert would fail. So, if you use that method you need to check for a failed insert and then run another update.

Or you can insert first, check for failure, then update if there is a row there already. There is no race condition caused failures here, but the method may not be what you want.

Think of which is the better way to "fail" from your apps perspective and chose.

Oh I apologise, I see what's happened, someone has merged two different threads, related, but different threads never the less.

OK, I'm going to start another thread for this, please don't merge them this time, whoever it was.