CODEWITHSUNDEEP
phpmysqlmagento
Nicekiwi
Nicekiwi

Reputation: 4807

Where is the table that holds the "Special Price" in Magento?

Im trying to update the "Special_price" and "price" in bulk with mySQL an a php script, I know the table and row that contains the "price" but not the one that contains the "special_price".

I've looks though the database itself and still no luck. Any ideas? I need the table name and the field name.

Upvotes: 12

Views: 21771

Answers (3)

semiprecious.com
semiprecious.com

Reputation: 1

Here is how to delete special price from a list of SKUs imported via a csv file into temp_import_sp_price_delete.

delete deci from catalog_product_entity_decimal deci, 
                 `catalog_product_entity` pr,
                 temp_import_sp_price_delete temp 
where temp.SKU= pr.SKU and pr.entity_id=deci.entity_id and deci.attribute_id=76

Upvotes: -2

Alana Storm
Alana Storm

Reputation: 166066

My membership in the Magento Question Answers Guild requires me to suggest you work on fixing your API errors instead of using plain old SQL to update the database. As mentioned elsewhere, updating the database directly might put Magento into a state not recognized by the system, which can lead to strange, infuriating errors.

That said, the special price value will be stored with the other product attribute values in the 

catalog_product_entity_decimal

table. This table has an attribute_id column, which has a foreign key relationship with the eav_attribute table. Look in the eav_attribute table for the attribute with the code special_price. That attribute_id and the product's entity_id should be enough to find the correct row in catalog_product_entity_decimal.

Keep in mind no row will exist if a product doesn't have a special_price set. Also keep in mind if a product has a special_price set at different scope levels that there may be more than one row.

Upvotes: 18

Mohammad Alsallal
Mohammad Alsallal

Reputation: 126

Special Price is an attribute of decimal type. First you need to get the attribute id, by applying this sql query: SELECT attribute_id FROM eav_attribute WHERE attribute_code='special_price';

Then, you can add a special price for any product by inserting a record into catalog_product_entity_decimal table.

Upvotes: 1

Related Questions