0

i have a big problem i have been trying to solve for hours without luck.

I need to edit or insert lines into an existing table and getting the data from another table, but the second table has multiple lines with same productid.

The structure of these tables are different, one of them as an unique id (i think this is the right definition, but i am not sure), meaning that each line has its own unique id (i am sorry but i am not able to explain this with proper language, so i hope you understand) and that you will not find 2 lines with the same id. Table 2 has another structure and it has no unique id, it only has 2 fields, productid and categoryid, and i have multiple lines with the same productid

The table from which i need to get data has the following structure: id cid

The table where i need to insert or update data has the following structure: productid categoryid

The problem is that table 2 has multiple productids, meaning that i can have more than 1 line with the same productid (ex. productid 20 categoryid 3 and again another line with productid 20 and categoryid 4), and in my existing table 2 there are already many productid entries that have more than 1 line with the sameproduct id, because this is used to select multiple categories for a single product, and i would like not to lose existing information but also i would like to understand how to recreate this table 2 from scratch inserting new lines.

I need table 2 to have at least 1 line with table 1 id as productid and table1 cid as categoryid, if possible without deleting existing data of multiple categories in table 2.

So i have 2 solutions:

  1. lose existing multiple categories information deleting table 2 and recreate it with some code similar to the code i used below inserting data from table 1, please check my code because i am a noob.

  2. update table 2 and avoid losing data for multiple category but assure that i have at least 1 entry with the main categoryid for each productid.

What i need is to set the categoryid field of table 2 equal to the cid field of table 1 where productid of table 2 is equal to id of table 1

So basically something like: 

INSERT INTO dz3_properties_product_category (productid, categoryid) SELECT 
id, cid FROM dz3_properties_products;

These last query gives me duplicated primary key error, i do not understand what to do..

Maybe something like 

update properties_products p join
properties_category l
on p.id = l.productid

set l.categoryid = p.cid;
jo.ker
  • 67
  • 7
  • You should add some examples to your question: table 1 structure (especially the primary key) + content, table 2 strucure (incl. primary key) + content and the desired result. It's hard to understand your intention from your text (as you already said yourself). You can probably use something like `on duplicate key update/ignore` or `where not exists (....)` to only insert data that does not exist yet, see e.g. [How to 'insert if not exists' in MySQL?](https://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql). – Solarflare Jun 21 '17 at 14:35
  • thank you, some freelancer dropped primary key on my table and now my query worked, but i am worried about not haing a primary key anymore, can you tell me if this will create other problems for me? – jo.ker Jun 21 '17 at 19:00
  • Since you didn't clarify your post, I am still not sure what you are trying to do exactly, what your data should look like and what you are using your data for, or if maybe the primary key was wrong before. So this might not be true for you. But generally: yes, dropping the primary key to get an incorrect query working instead of correcting the query (e.g. by using any of the 11 answers in my link) will very very very very likely create other problems for you. – Solarflare Jun 22 '17 at 06:46

0 Answers0