Faced a problem. There are queries in the database, but I don’t understand how to make it in one fil-CodePudding

First request. If product_id has only one option_id equal to 10063 and does not have 10101 then write TEXT-1 to the database

$this->db->query("UPDATE " . DB_PREFIX . "product SET oct_stickers = IF(product_id IN (SELECT product_id FROM " . DB_PREFIX . "ocfilter_option_value_to_product WHERE option_id IN ('10063', '') GROUP BY product_id HAVING COUNT(*) = 1), 'TEXT-1', '')");

Second request. If product_id has only one option_id equal to 10101 and does not have 10063 then write to TEXT-2 database

$this->db->query("UPDATE " . DB_PREFIX . "product SET oct_stickers = IF(product_id IN (SELECT product_id FROM " . DB_PREFIX . "ocfilter_option_value_to_product WHERE option_id IN ('10101', '') GROUP BY product_id HAVING COUNT(*) = 1), 'TEXT-2', '')");

Third request. If product_id has exactly two option_id - 10101 and 10063 then write to TEXT-3 database

$this->db->query("UPDATE " . DB_PREFIX . "product SET oct_stickers = IF(product_id IN (SELECT product_id FROM " . DB_PREFIX . "ocfilter_option_value_to_product WHERE option_id IN ('10101', '10063') GROUP BY product_id HAVING COUNT(*) = 2), 'TEXT-3', '')");

Sorry, I'm not a programmer. I wrote this request for two days. That's all I had the knowledge for :( Sorry for bad english.

CodePudding user response：

Instead of using IF's you can use one CASE WHEN like

$this->db->query("UPDATE " . DB_PREFIX . "product 
SET oct_stickers = CASE WHEN
                      product_id IN (SELECT product_id FROM " . DB_PREFIX . "ocfilter_option_value_to_product WHERE option_id IN ('10063', '') GROUP BY product_id HAVING COUNT(*) = 1) THEN  'TEXT-1'

WHEN product_id IN (SELECT product_id FROM " . DB_PREFIX . "ocfilter_option_value_to_product WHERE option_id IN ('10101', '') GROUP BY product_id HAVING COUNT(*) = 1) THEN  'TEXT-2'
WHEN product_id IN (SELECT product_id FROM " . DB_PREFIX . "ocfilter_option_value_to_product WHERE option_id IN ('10101', '10063') GROUP BY product_id HAVING COUNT(*) = 2) THEN 'TEXT-3'
ELSE '' END ");