cat update_product_attributes_using_sql.sql
update catalog_product_entity_int -- select * from catalog_product_entity_int
set catalog_product_entity_int.value = 1
where (catalog_product_entity_int.attribute_id = 97) and catalog_product_entity_int.entity_id = (
select entity_id from (
SELECT product.entity_id as entity_id FROM bitnami_magento.catalog_product_entity product
inner join catalog_product_entity_decimal price on price.entity_id = product.entity_id and (price.attribute_id in (78))
inner join catalog_product_entity_int status_t on (status_t.entity_id = product.entity_id) and (status_t.attribute_id=97)
where (price.value >= 20 and price.value <= 21)
and (status_t.value = 1)
Category Archives: Magento 2 : Operations using SQL
Magento 2 : Operations using SQL
Magento 2: Clear Images in database
truncate table bitnami_magento.catalog_product_entity_gallery;
truncate table bitnami_magento.catalog_product_entity_media_gallery_value;
truncate table bitnami_magento.catalog_product_entity_media_gallery_value_to_entity;
truncate table bitnami_magento.catalog_product_entity_media_gallery_value_video;
no warranty
given as is
Magento 2: Update data for a custom product attribute
use bitnami_magento;
SELECT * FROM bitnami_magento.catalog_product_entity_varchar
where ( attribute_id = 73 ) -- and (value = '')
limit 10
replace into bitnami_magento.catalog_product_entity_varchar (attribute_id, store_id, entity_id, value )
SELECT 181, 0, product.entity_id, left(concat("https://www.amazon.com/s?k=", product.value),255)
FROM bitnami_magento.catalog_product_entity_varchar product
inner join catalog_product_entity_int status_t on ( status_t.entity_id = product.entity_id ) and ( status_t.attribute_id = 97 and status_t.value = 1 )
where (product.attribute_id=73) -- and (value = '') -- 73 = product name
SELECT * FROM bitnami_magento.catalog_product_entity_varchar
where ( attribute_id = 181 ) -- and (value = '')
order by value_id desc
limit 10
-- Update Uk Urls -----
use bitnami_magento;
SELECT * FROM bitnami_magento.catalog_product_entity_varchar
where ( attribute_id = 73 ) -- and (value = '')
limit 10
replace into bitnami_magento.catalog_product_entity_varchar (attribute_id, store_id, entity_id, value )
SELECT 180, 0, product.entity_id, left(concat("https://www.amazon.co.uk/s?k=", product.value), 255)
FROM bitnami_magento.catalog_product_entity_varchar product
inner join catalog_product_entity_int status_t on ( status_t.entity_id = product.entity_id ) and ( status_t.attribute_id = 97 and status_t.value = 1 )
where (product.attribute_id=73) -- and (value = '') -- 73 = product name
SELECT * FROM bitnami_magento.catalog_product_entity_varchar
where ( attribute_id = 180 ) -- and (value = '')
order by value_id desc
limit 10
-- *
select max(value_id) from bitnami_magento.catalog_product_entity_varchar
Magento 2: Misc. Image related SQLs
select * from bitnami_magento.catalog_product_entity_media_gallery_value_to_entity
where value_id in (232574, 242521)
-- 232574, 242521
select * from bitnami_magento.catalog_product_entity_media_gallery_value_to_entity
where value_id in (232574, 242521)
-- where value like '%ud6vtf2a_5ezdxueqvnr%'
--select * from bitnami_magento.catalog_product_entity_media_gallery_value_to_entity
select * from bitnami_magento.catalog_product_entity_media_gallery
-- where value_id in (237733, 237734)
where value like '%ud6vtf2a_5ezdxueqvnr%'
update bitnami_magento.catalog_product_entity_media_gallery
set value = '/u/d/ud6vtf2a_5ezdxueqvnr1dyny_1.jpeg'
where value = '/u/d/ud6vtf2a_5ezdxueqvnr1dyny.jpeg' and value_id=242521
select * from bitnami_magento.catalog_product_entity_media_gallery_value
-- where value_id in (232574, 242521)
where entity_id in (70414)
select * from bitnami_magento.catalog_product_entity_media_gallery_value
where label is not null
70414 shopforsoul-PTO_06RSLOC0
237733, 237734
insert into bitnami_magento.catalog_product_entity_media_gallery_value values(237733, 0, 70414, '', 3, 0, 37613)
select max(record_id) from catalog_product_entity_media_gallery_value
insert into catalog_product_entity_media_gallery_value_to_entity values (237733, 70414)
select distinct(attribute_id) from bitnami_magento.catalog_product_entity_media_gallery
Magento 2: Deactivate Products without images
update catalog_product_entity_int
-- select * from catalog_product_entity_int
set catalog_product_entity_int.value = 2
where (catalog_product_entity_int.attribute_id = 97) and catalog_product_entity_int.entity_id in (
select entity_id from (
Select distinct(product.entity_id)
FROM bitnami_magento.catalog_product_entity product
left join catalog_product_entity_media_gallery_value_to_entity valentity on valentity.entity_id = product.entity_id
left join catalog_product_entity_media_gallery image on image.value_id = valentity.value_id and (image.attribute_id in (90))
inner join catalog_product_entity_int status_t on (status_t.entity_id = product.entity_id) and (status_t.attribute_id=97 and status_t.value=1)
where product.entity_id not in (select entity_id from catalog_product_entity_media_gallery_value_to_entity)
) as c )