Help with SQL query

[Kolopsia]

Hello,

 

I need to run a sql query that will fill in ean code in all products by using the ean code from one of the combinations.

 

The main product lack ean code but all combinations have it, i just cant copy and past ean code for over 5000 product so can this be done with sql query?

 

 

Any ean code from the combinations will work for the main product, i just tested this with google shopping.

[Scully]

One should always mention the prestashop version. If it's 1.7. I wouln't even answer on a topic. It would make things much easier here...

[Kolopsia]

Its version 1.6.1.9

[Scully]

The question is a very good topic to learn some basics in MySQL:

This is an example in terms of SQL - it updates table1 with a field taken from table 2.

UPDATE table1
SET table1.price=(SELECT table2.price
FROM table2
WHERE table2.id=table1.id AND table2.item=table1.item);

Your tables are named ps_product (corresponds to table1) and ps_product_attribute (corresponds to table 2) if you have default table prefix set as ps_.

You can post your result here if you want me to check it.

Edited August 24, 2017 by Scully (see edit history)

[Kolopsia]

The question is a very good topic to learn some basics in MySQL:

This is an example in terms of SQL - it updates table1 with a field taken from table 2.

UPDATE table1
SET table1.price=(SELECT table2.price
FROM table2
WHERE table2.id=table1.id AND table2.item=table1.item);

Your tables are named ps_product (corresponds to table1) and ps_product_attribute (corresponds to table 2) if you have default table prefix set as ps_.

You can post your result here if you want me to check it.

 

Hello, why "price" shouldn't it be ean13?

[selectshop.at]

Why you don't import the EAN by csv ? BTW also combinations, as they are own products, have own EAN codes, besides Prestashop SEO architecture, is to don't produce double content, therefore the combination content (for Googleshop, facebook, etc.)  are generated too by sizing as own product. Therefore is not advisable to use same EAN for several combinations. In this case it is correct to let EAN of master product empty and add the EAN on each combination. This is best SEO.

[Scully]

I just took a simple example of how to update a table with values coming from another table. And yes, ean13 would be the field you are looking for.

[selectshop.at]

Hello, why "price" shouldn't it be ean13?

EAN is standardized. It is a 13 char. number indetifying a product. It cannot be a price.

 

See here: https://en.wikipedia.org/wiki/International_Article_Number

[Scully]

I know what ean13 is. The goal was to help Kolopsia in understanding some SQL. If we just post the complete result, the learning effect would be near to zero. Hence my approach.

 

Using CSV is another option. However you first needed to get the ean13 retrieved from the system before updating the product table.

[Kolopsia]

Why you don't import the EAN by csv ? BTW also combinations, as they are own products, have own EAN codes, besides Prestashop SEO architecture, is to don't produce double content, therefore the combination content (for Googleshop, facebook, etc.)  are generated too by sizing as own product. Therefore is not advisable to use same EAN for several combinations. In this case it is correct to let EAN of master product empty and add the EAN on each combination. This is best SEO.

The products are imported by a module and all master products have empty EAN fields. I will not duplicate ean for several combinations but use one of the combinations ean for the main product EAN, because if the master product does not have EAN code it will not be exported to google shopping. If i export one product per combination it will not work with retargeting module " Google Remarketing + Dynamic" so this is the only solution i can find.

[Kolopsia]

I just took a simple example of how to update a table with values coming from another table. And yes, ean13 would be the field you are looking for.

I appreciate your help

 

Is the final result correct now? 

 

UPDATE ps_product

SET ps_product.ean13=(SELECT ps_product_attribute.ean13

FROM ps_product_attribute

WHERE ps_product_attribute.id=ps_product.id AND ps_product_attribute.item=ps_product.item);

 

 

will this get the ean13 code from any one of the combinations randomly?

[Scully]

Not so bad, I have some adaptions:

UPDATE ps_product
SET ps_product.ean13=(SELECT ps_product_attribute.ean13
FROM ps_product_attribute
WHERE ps_product_attribute.id_product=ps_product.id_product limit 0,1);

• id does not exist, the field is named id_product
• the item fields does not exist in our tables, it's just and example because we need more than one field in the where condition
• the subquery must return 1 row, i limited this by using limit 0,1
• yes, the row taken for the update is random.
• It would make sense to ensure ean is set in fact and no updates with zero or null values take place.
• Don't run without having taken a backup first.

Edited August 24, 2017 by Scully (see edit history)

[Kolopsia]

 

Not so bad, I have some adaptions:

UPDATE ps_product
SET ps_product.ean13=(SELECT ps_product_attribute.ean13
FROM ps_product_attribute
WHERE ps_product_attribute.id_product=ps_product.id_product limit 0,1);

• id does not exist, the field is named id_product
• the item fields does not exist in our tables, it's just and example because we need more than one field in the where condition
• the subquery must return 1 row, i limited this by using limit 0,1
• yes, the row taken for the update is random.
• It would make sense to ensure ean is set in fact and no updates with zero or null values take place.
• Don't run without having taken a backup first.

 

 

Hello,

I did run this on the test version of the site and there is a problem. Yes the query was successful in adding ean code on all products WITH combinations. But the ean code was removed on all singel products with no combinations. So can we add another condition to the query so that it will only effect products with combination OR products with empty ean13 fields?

[Scully]

Exactely what I meand with this and highlighted it:

 

It would make sense to ensure ean is set in fact and no updates with zero or null values take place

 

You need to add an additional where clause to the subquery

AND ps_product_attribute.ean13 is not NULL limit ...

[Kolopsia]

 

Exactely what I meand with this and highlighted it:

 

It would make sense to ensure ean is set in fact and no updates with zero or null values take place

 

You need to add an additional where clause to the subquery

AND ps_product_attribute.ean13 is not NULL limit ...

So the last line should be 

WHERE ps_product_attribute.id_product=ps_product.id_product limit 0,1 AND ps_product_attribute.ean13 is not NULL);

 

Because its not working. i'm sure that i'm doing something wrong here...

[Kolopsia]

Okay this is what the last line should be like 

 

WHERE ps_product_attribute.id_product=ps_product.id_product AND ps_product_attribute.ean13 is not NULL limit 0,1)

 

But unfortunately that didnt work , single products with no combination still got their EAN code removed.

[Scully]

You have to rollback your database. Once removed they don't come back, regardless of the SQL discussed here. That is also why I highlighted the need of an additional WHERE clause and recommended to backup the database first.

Edited August 24, 2017 by Scully (see edit history)

[Kolopsia]

Yes i know but i added EAN code manully to few single products and tested the query again and it still did remove the ean code from these products.

[Scully]

Pls. post the full SQL again. It might be you have an empty string like "" in your ean13 for combinations, which isn't the same as a NULL string. You could try to check for the length of the ean13 code.

Edited August 24, 2017 by Scully (see edit history)

[Kolopsia]

This is the full query

 

UPDATE ps_product

SET ps_product.ean13=(SELECT ps_product_attribute.ean13 FROM ps_product_attribute 

WHERE ps_product_attribute.id_product=ps_product.id_product AND ps_product_attribute.ean13 is not NULL limit 0,1);

[Scully]

UPDATE ps_product
    SET ps_product.ean13=(SELECT ps_product_attribute.ean13 FROM ps_product_attribute 
    WHERE ps_product_attribute.id_product=ps_product.id_product AND ps_product_attribute.ean13 is not NULL limit 0,1)
    WHERE ps_product.ean13 is NULL or ps_product.ean13 = '';

no spaces in between here: ''

[Kolopsia]

Thank you! that did work.

 

So basically the problem was that when ean13 does not exist the field was not NULL but simply empty thats why '' worked.

[Scully]

Almost, but not exactely. The new query simply does not touch products where ean is already set. To make this works in any case, I used the NULL and the '' condition. If you have a new product, the initial value will be NULL. But if you delete an old ean13, it then would change its value to an empty string like ''.

 

You could mark the topic as solved.

Edited August 24, 2017 by Scully (see edit history)