0

I want to copy/update values from table_a to another row of table_b WHERE table_a.id = table_b.id AND table_a.name = table_b.name

I have 2 tables price_drop_apps_temp and price_drop_apps that have the same structure 

CREATE TABLE IF NOT EXISTS `price_drop_apps_temp` (
  `application_id` int(11) NOT NULL DEFAULT '0',
  `storefront_id` int(11) NOT NULL DEFAULT '0',
  `price_1` decimal(9,3) DEFAULT NULL,
  `price_2` decimal(9,3) DEFAULT NULL,
  `price_3` decimal(9,3) DEFAULT NULL,
  `price_4` decimal(9,3) DEFAULT NULL,
  `price_5` decimal(9,3) DEFAULT NULL,
  `price_6` decimal(9,3) DEFAULT NULL,
  PRIMARY KEY (`application_id`,`storefront_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci")

The table price_drop_apps_temp contains updated application_id and storefront_id.

I need to copy from table price_drop_app all values from column price_2 to table price_drop_apps_temp column price_1 where application_id AND storefront_id match in both tables and so on for some other columns. If price_drop_apps_temp has no matching application_id AND storefront_id the values should remain NULL.

I tried following but its not working and I am also not sure if I should use UPDATE or INNER JOIN. My SQL skills are very limited so any help appreciated.

INSERT INTO price_drop_apps_temp (price_1,price_2,price_3) SELECT price_2,price_3,price_4 FROM price_drop_apps WHERE price_drop_apps_temp.application_id = price_drop_apps.application_id AND price_drop_apps_temp.storefront_id = price_drop_apps.storefront_id
VolkaRacho
  • 191
  • 1
  • 2
  • 13

1 Answers1

1

I'm not quite sure to understand all your needs. But...

I want to copy/update values from table_a to another row of table_b WHERE table_a.id = table_b.id AND table_a.name = table_b.name

You basically want to update the columns price[1-3] of one table with those of the other table? I would try something like that:

UPDATE price_drop_apps_temp AS dst, price_drop_apps AS src
  SET dst.price1 = src.price1, dst.price2 = src.price2, dst.price3 = src.price3
  WHERE dst.application_id = src.application_id 
    AND dst.storefront_id = src.storefront_id

The WHERE clause has probably to be adjusted depending on your specific needs (table_a.id/table_a.name ?!?). But that's the spirit...

If the source table contains some rows not present in the second table, you will probably have to add a second pass to INSERT them:

INSERT IGNORE INTO price_drop_apps_temp SELECT * FROM price_drop_apps;
Sylvain Leroux
  • 50,096
  • 7
  • 103
  • 125
  • Hm with your statement I get the following error: `#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE (dst.application_id, dst.storefront_id) = (src.application_id, src.storefr' at line 3` – VolkaRacho Aug 07 '13 at 20:32
  • still gave me an error, but found a good solution http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server?rq=1 but thank you very much for taking the time! – VolkaRacho Aug 08 '13 at 08:28
  • @VolkaRacho Maybe a stupid typo (I noticed an extra `,`) as this solution is basically the same as the one you mentioned. Probably (?) fixed now... – Sylvain Leroux Aug 08 '13 at 08:35