Update Table With An Empty Column With Data From Another Table using a shared id from a third table

Question

Hi Guys new to this and need pointed in the right direction.

DB1 product_description [product_id, name, description]

*DB1.product_description.product_id is linked to category_id in a separate product_to_category_id table. category_id in table1 == cat_id in DB2.Inventory

  DB1  product_description

------------------------------------------------------
| product_id     | name          |  description       |
-------------------------------------------------------
| 999            | product999    | description text   |
| 1000           | product1000   |                    |
| 1001           | product1001   |                    |
| 2000           | product2000   |                    |
-------------------------------------------------------

DB2 Inventory [productId, name, description, cat_id]

DB2
Inventory
------------------------------------------------------------------
| productId     | name          |  description       | cat_id    |
-----------------------------------------------------------------
| 999            | product999    | description text  | 236       |
| 1000           | product1000   | description text2 | 237       |
| 1001           | product1001   | description text3 | 237       |
| 2000           | product2000   | description text4 | 456       |
-----------------------------------------------------------------


DB1
product_to_category
---------------------------------
| product_id     | category_id   |
---------------------------------
| 999            | 236           |
| 1000           | 237           |
| 1001           | 237           |
| 2000           | 456           |
---------------------------------

I am looking to copy the "description" data from DB2 and place it into the "description" in DB1 preferably using WHERE cat_id >=237 <=456

I was hoping to use the category id because I can move products over and insert meta dat at the same time. cat_id is a collection of products around 200 or using the productId but I would need to update the other fields separately

UPDATE DB1.product_description
SET description = (SELECT description
FROM DB2.Inventory
WHERE `cat_id` =2616);

it gives the 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 'SET description = (SELECT description FROM DB2.Inventory WHEREcat_id=2616)' at line 2

removed the comma thanks strawberry ;) now get the error;

#1242 - Subquery returns more than 1 row

I have 30,000 products listed with descriptions, but need to integrate another 2000 products into the database, without disturbing products that have data already in the description field.

I have tried various post on this site before posting but can't figure out how to use the cat_id form the separate table. Or even if its possible. If someone would be kind enough to point out where I'm going wrong i would be grateful. I have spent the last three days trying to get the description field filled but with no joy after viewing tons of posts on the forum

Thanks again

HTT

possible duplicate of [MySQL - UPDATE query based on SELECT Query](http://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query) — Jay Blanchard, Jun 05 '15 at 12:41
Hi Jay Thank you for your quick response. I did have a look at the link above before posting but I could not get to grips with the cat_id. is it because I have not linked cat_id with category_id. I did try to use productId for WHERE productId = product_id but got a primary key error — HoldThatThought, Jun 05 '15 at 13:08

score 0 · Accepted Answer · answered Jun 05 '15 at 23:09

0

Please try the following on your test environment:

UPDATE 
  DB1.product_description
  INNER JOIN DB2.Inventory
     ON DB2.Inventory.productId = DB1.product_description.product_id
SET 
  DB1.product_description.description = DB2.Inventory.description
WHERE 
  DB2.Inventory.cat_id = 2616;  

/* 
   or for the range of records:  
   WHERE (DB2.Inventory.cat_id >= 237 AND DB2.Inventory.cat_id <= 456)
*/

I think Mr @Jay Blanchard pointed to the right direction.

Hope it may help at least a little.

answered Jun 05 '15 at 23:09

Hi T_G It worked a treat thank you so much :) Im sure Jay pointed me in the right direction, but I really needed more of a walk through and your code was perfect. I tested it on catalog 2616 as it was a small collection but will be able to use your amended code for the range as well. It was very kind of you to take the time to help. You have know idea how stressed this got me. Thanks again T_G ;) – HoldThatThought Jun 06 '15 at 00:03
No problem. I'm glad to help then :) – Jun 06 '15 at 05:23

Update Table With An Empty Column With Data From Another Table using a shared id from a third table

1 Answers1