1

Currently my store has more 1500 products. I want to update my tier prices for each product by one. Is there any way to directly update database or by a script.

Thanks

  • I did using php script with Marius help. Please see our comments in Marius answer. I added my answer separately. – cha Jul 17 '13 at 14:25
5

All the tier prices are stored in the table catalog_product_entity_tier_price. The columns mean the following:

  • value_id - table auto increment
  • entity_id - the product id
  • all_groups - 0/1 flag that tells if the prices apply to all customer groups
  • customer_group_id - the id of the customer group the prices are applied. If all_groups is 1 this has no value. If you have one price that should apply to only 2 customer groups add 2 different records in the table one for each customer group
  • qty - represents the minimum quantity for which the tier price is applied.
  • value - is the actual price value
  • website_id - the id of the website for which the prices are applied. 0 for all websites.

I know it's not a good practice to edit the DB directly, but in this case, when there is only one table involved, the risk is minimum. I've done this with success in the past. If you manage to format you data in the same format of the columns described above you can directly insert/update data in this table.
If you plan to take this approach backup your database before starting.
After you are done a price reindex is required.

| improve this answer | |
  • I think it is possible to update directly by issuing update query because i want to update all the product's qty by one. update catalog_product_entity_tier_price set qty = qty+1; – cha Jul 17 '13 at 12:37
  • that could work. But still...please backup first :). – Marius Jul 17 '13 at 12:38
  • I got this error. ERROR 1062 (23000): Duplicate entry '182-1-0-2.0000-0' for key 'E8AB433B9ACB00343ABB312AD2FAB087' Can help on this please? – cha Jul 17 '13 at 12:45
  • oh yeah...this can happen if you already have a value for qty 2 and you are trying to change the qty for a record from 1 to 2. You can try to remove the unique keys on that table and add them again when you are done. – Marius Jul 17 '13 at 12:51
  • I tried to remove unique keys but there are few errors. Then I tried this using php script. Retrieved data using this query - "select * from catalog_product_entity_tier_price order by value_id desc, qty" then loop them and update date using this query - update catalog_product_entity_tier_price set qty = qty+1 where value_id=".$row['value_id'] Thanks for help Marius.. – cha Jul 17 '13 at 14:19
1

I use following script to update tier price with help from Marius.

/*
  * Tier price update directly
  */


 $con = mysqli_connect("localhost", "user", "password", "database");
 // Check connection
 if (mysqli_connect_errno()) {
     echo "Failed to connect to MySQL: " . mysqli_connect_error();
 }
//get higher tier qty first update it. Then we don't get duplicate values error. 
//Because value_id, all_groups, customer_group_id, qty and website_id are unique.
 $query = "select * from `catalog_product_entity_tier_price` order by  value_id desc, qty";
 $results = mysqli_query($con, $query);
 $row_count = 0;
 $error_row_count = 0;
 while ($row = mysqli_fetch_assoc($results)) {
     $update_query  = "";
     $update_query = " update `catalog_product_entity_tier_price` set qty = qty+1 where value_id=".$row['value_id'];
 //    echo $update_query;
     if(!mysqli_query($con, $update_query)){
         ++$error_row_count;
         echo 'Value id = '.$row['value_id']." -- Tier price =".$row['value']." -- Qty =".$row['qty']." -- Entity id = ".$row['entity_id']."<br />";
     }
     ++$row_count;
 }
 echo "Total records successe = ".$row_count."<br />";
 echo "Error records = ".$error_row_count;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.