scp-blackfriday-search.sql

select p.id, p.sku, p.upc, json_get(p.custom_data, 'pricing_guide') pricing_guide, p.long_name
from products p
inner join amazonproducts ap on ap.product_id = p.id
where 
    (
        json_get(p.custom_data, 'life_cycle', 0) != 'Pre-Release'
        or (
            json_get(p.custom_data, 'life_cycle', 0) != 'Active'
            and json_get(p.custom_data, 'product_group') != 'Machines'  
        )
    )
    and ap.buybox_status = 3000
order by p.id desc;

select p.id, p.sku, p.upc, json_get(p.custom_data, 'pricing_guide') pricing_guide
from products p
inner join amazonproducts ap on ap.product_id = p.id
where 
    json_get(p.custom_data, 'life_cycle', 0) = 'Active'
    and json_get(p.custom_data, 'product_group') = 'Machines'
    and ap.buybox_status = 3000
order by p.id desc;

scp-blackfriday-start.sql

update products p
inner join amazonproducts ap on ap.product_id = p.id
set p.custom_data = replace(replace(p.custom_data, 'low_price', 'lowestless5'), 'map_price', 'lowestless5')
where 
    (
        json_get(p.custom_data, 'life_cycle', 0) != 'Pre-Release'
        or (
            json_get(p.custom_data, 'life_cycle', 0) != 'Active'
            and json_get(p.custom_data, 'product_group') != 'Machines'  
        )
    )
    and ap.buybox_status = 3000
;

update products p
inner join amazonproducts ap on ap.product_id = p.id
set p.custom_data = replace(replace(p.custom_data, 'low_price', 'match'), 'map_price', 'match')
where 
    json_get(p.custom_data, 'life_cycle', 0) = 'Active'
    and json_get(p.custom_data, 'product_group') = 'Machines'
    and ap.buybox_status = 3000
;