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
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.