The Poetics Of Coding

If you want to update price of magento whole catalog a SQL statement will be enough to run.
Tables involve are ‘catalog_product_entity_decimal’ containing product price value, msrp value etc
we also need to get price attribute id.

SELECT attribute_id FROM eav_attribute eav
WHERE eav.entity_type_id = 4 
AND eav.attribute_code = 'price'

Now let’s merge this query to price value update
NOTE::- Replace ‘new_price_value’ with your new price

UPDATE catalog_product_entity_decimal val
SET  val.value = 'new_price_value'
WHERE  val.attribute_id = (
     SELECT attribute_id FROM eav_attribute eav
     WHERE eav.entity_type_id = 4 
     AND eav.attribute_code = 'price'

Never run sql update query on live database directly, always test first on local or testing server.

At midnight got message from a client that his site started the installation process while accessing.

Items to check:
1. First check you local.xml under app/etc if exists
2. check “


” tag under “


” exists, if yes
3. check date in this tag under “install”

<date><![CDATA[Sat, 16 May 2015 08:14:53 +0000]]></date>

should be strtotime or unix readable.

All these items were fine, so what next?

Checked the system.log file under var/log and find a Fatel Error something like this “PHP Fatal error: SOAP-ERROR: Parsing WSDL: Couldn’t load

Problem was with our SOAP API access, we were using a SAP software to manage order and warehouse inventory. Call from this over API set a fetal error.

so try to flush the cache and session but no luck and at last just press server restart button, that kill all API requests and everything was fine.

An easy way to update custom option in bulk is by using SQL

If you have a custom option with Title “Extended Warranty
with price options
1 year – $20
2 years – $30

you can do this with three magento tables
1. Want to update custom option title make a query on table “catalog_product_option_title”
2. Want to update price title make a query on table “catalog_product_option_type_title”
3. Want to update price make a query on table “catalog_product_option_type_price”

The simplest query is

UPDATE Tbl_Name set field_name = TRIM(Replace(Replace(Replace(field_name,'\t',''),'\n',''),'\r',''));

Replace category_id_here with you your category id.

SELECT p.entity_id AS prod,
p.sku AS sku,
cp.category_id AS cp_category_id,
cp.`position` AS cp_position,
c.`entity_id` AS c_category_id,
cat_varchar.value AS CatName,
c.`entity_type_id` AS c_entity_type_id,
c.`attribute_set_id` AS c_attribute_set_id,
c.`parent_id` AS c_parent_id,
c.`path` AS c_path,
c.`position` AS c_position,
c.`level` AS c_level,
c.`children_count` AS c_children_count
FROM catalog_product_entity AS p
LEFT JOIN catalog_category_product AS cp
ON p.entity_id = cp.product_id
LEFT JOIN catalog_category_entity AS c
ON cp.category_id = c.entity_id
LEFT JOIN catalog_category_entity_varchar AS cat_varchar
ON     c.entity_id = cat_varchar.entity_id
AND cat_varchar.attribute_id = 111
LEFT JOIN catalog_product_entity_varchar AS prod_varchar
ON     p.entity_id = prod_varchar.entity_id
AND prod_varchar.attribute_id = 96
AND c.entity_id = category_id_here

I was working on a project and we imported more than 90 thousand products; suddenly, the client asked to update SKU format for 21 thousand products.

I searched for the solution on the internet and found many duplicate solutions, in fact it was a single solution and everyone was copying. When I checked this solution I realized that this script is not for me, as it first check the SKU in database and after that update and save, so 21 thosand products means 52 thousand calls to DB.

Now check my solution.

1. Create CSV with 2 columns. One for the old SKU and other for new SKU

2. Create a temp table in your magento DB, lets say it’s name is sku_tmp

3. Import your CSV to this table, using your favorit DB tool (phpmyadmin…. i used sqlyog)

4. Now run this query “UPDATE catalog_product_entity AS cpe, sku_temp AS tmp SET cpe.sku = tmp.sku2  WHERE  cpe.sku = tmp.sku ”

Hurrah…………. 21 thousand rows updated in just seconds……..


On my local it needs to update in windows so i just reinstall it, it removes the wamp server too. I just downloaded the new version and installed it.

With this new installation phpmyadmin version and mysql version updated too. So like i did in past, I just copied the database directory and pasted it in new version, but problem was

1. Database with all table and table with all columns was in left side

2. Onclick in left side it shows error #1146 table doesn’t exist, strange.

after a lot of searching and reading mysql documentation I came to realize that there are three other files out of database directory

1. ibdata1

2. ib_logfile0, ib_logfile01

so I need to replace these 3 files in your new version of database aswell and problem will be solved.

No thanks needed as I am great when helping others……….. :)


Get every new post delivered to your Inbox.

%d bloggers like this: