Changing product combinations impact on price in SQL

[marktomm90]

Hello!

 

I have a shop with a lot of products which have a lot of combinations and prices for specific combinations vary (depending on the size, e.g. shoes with size 20-25 are 40EU, with 26-30 are 44EU).

 

Changing every combination 'impact on price' one by one is a long boring task.

 

So I wondered if there is a way to do it faster by modifying the corresponding SQL tables. I modified some 'impact on price' values for a specific product in back office (so I could later be sure I found the right place in SQL).

 

I modified 'impact on price' value (tax. excl.) of two combinations with value '5' for a product with reference 490-01 and found relevant entries in 'ps_product_attribute' table.

mysql> SELECT reference, price FROM ps_product_attribute WHERE reference='490-01' ;
+-----------+-----------+                                                                                                            
| reference | price     |                                                                                                            
+-----------+-----------+                                                                                                            
| 490-01    |  0.000000 |
| 490-01    |  0.000000 |
| 490-01    |  0.000000 |
| 490-01    |  0.000000 |
| 490-01    |  0.000000 |
| 490-01    |  5.000000 |
| 490-01    |  5.000000 |
| 490-01    |  0.000000 |
| 490-01    |  0.000000 |
| 490-01    |  0.000000 |
| 490-01    |  0.000000 |
| 490-01    |  0.000000 |
| 490-01    |  0.000000 |
| 490-01    |  0.000000 |
| 490-01    |  0.000000 |
| 490-01    |  0.000000 |
| 490-01    |  0.000000 |
+-----------+-----------+

After modifying the same product 'impact on price' values some more and seeing relevant changes in that table I was sure that I am in the right place.

 

But the problem is that when I tried to change these values via SQL:

UPDATE ps_product_attribute SET price=11 WHERE reference='490-01' AND id_product_attribute >= 1134;

This made the table look like the following:

mysql> SELECT reference, price FROM ps_product_attribute WHERE reference='490-01' ;
+-----------+-----------+                                                                                                            
| reference | price     |                                                                                                            
+-----------+-----------+                                                                                                            
| 490-01    |  0.000000 |
| 490-01    |  0.000000 |
| 490-01    |  0.000000 |
| 490-01    |  0.000000 |
| 490-01    |  0.000000 |
| 490-01    |  5.000000 |
| 490-01    |  5.000000 |
| 490-01    |  0.000000 |
| 490-01    |  0.000000 |
| 490-01    |  0.000000 |
| 490-01    |  0.000000 |
| 490-01    | 11.000000 |
| 490-01    | 11.000000 |
| 490-01    | 11.000000 |
| 490-01    | 11.000000 |
| 490-01    | 11.000000 |
| 490-01    | 11.000000 |
+-----------+-----------+

I basically changed the SQL the same way it would get changed if I modified 'impact on price' via back office, but it doesn't seem to work. After opening that product combination in back office I don't have these changes there (only those two '5.00' are there, which were modified in back office) and the front end also doesn't show the '11.00' impact on price for these specific combinations.

 

Can anyone guide me what I am doing worng here?

 

Thank You!

[thepan]

My first guess would be cache. Deactivate and delete cache and take a look.

[marktomm90]

@thepan:

 

Thank You for Your suggestion, I had caching turned off, that didn't solve the problem.

 

 

I found that there is another sql table named 'ps_product_attribute_shop' which, if I understand it the right way, also stores 'price impact' data (among other things) for a particular shop. After making the same changes to 'price' field there:

mysql> UPDATE ps_product_attribute_shop SET price=11 WHERE id_shop = 1 AND id_product_attribute >= 1134;

the corresponding values changed in the back office and in the front end.