1

I have create custom attribute in product upc

I want custom query (sql) to get SKUs and UPCs

Can anyone tell me the related tables from where I can get data, or if anyone can share query that would be great.

2

Magento uses Eav structure to save the product attribute value.

First, you have to find out your attribute type of upc.So, you have open eav_attribute and run the below for getting backend_type]

SELECT attribute_id,backend_type,attribute_code FROM eav_attribute where entity_type_id =4 and attribute_code ='upc'

Suppose, this attribute backend_type is varchar then magento is save the value for products of this attribute at catalog_product_entity_varchar

Magento is save product lie entity wise like

Date time type attribute  to catalog_product_entity_datetime
Decimal/Price type attribute to catalog_product_entity_decimal
Image type attribute to  catalog_product_entity_gallery
Dropdown type attribute  to catalog_product_entity_int
Image type catalog_product_entity_media_gallery
Image type catalog_product_entity_media_gallery_value
Image type catalog_product_entity_media_gallery_value_to_entity
text type attribute  catalog_product_entity_text
tier price catalog_product_entity_tier_price
Varchar type attribute  to  catalog_product_entity_varchar

So, you have to find the appropriate table of this attribute then run below Query like:

SELECT * FROM catalog_product_entity_varchar where attribute_id =(SELECT attribute_id FROM eav_attribute where entity_type_id =4 and attribute_code ='name')

Here I have done for the name attribute.Fpr your case,you have to change catalog_product_entity_varchar respect to your backend type of attribute.

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.