CODEWITHSUNDEEP
mysql
user3337635
user3337635

Reputation: 103

mysql syntax error on sub query in insert values

whats wrong with this query when i run they got a syntax error 

INSERT IGNORE INTO an_catalogsearch_fulltext (product_id, store_id, data_index) 
SELECT DISTINCT ca_ent.entity_id as product_id, 
4 as store_id, 
CONCAT( 
ifnull(ca_var.value,''),' ',
ifnull(ca_ent.sku,''), ' ', 
ifnull(
    (
        SELECT GROUP_CONCAT( ca_ent2.sku) FROM `an_catalog_product_entity` ca_ent2 LEFT JOIN `an_catalog_product_super_link` ca_sup 
        ON ca_sup.product_id = ca_ent2.entity_id WHERE ca_sup.parent_id = ca_ent.entity_id GROUP BY ca_sup.parent_id
    ),'') ,' ', 
ifnull(
    (
        SELECT GROUP_CONCAT( ca_ent3.sku) FROM `an_catalog_product_entity` ca_ent3 LEFT JOIN `an_catalog_product_link` ca_lin 
        ON ca_lin.linked_product_id = ca_ent3.entity_id WHERE ca_lin.product_id = ca_ent.entity_id GROUP BY ca_lin.product_id
    ),'') ,' ', 
ifnull(ca_text.value,'') , ' ', 
ifnull(
    select option_value.value from `an_eav_attribute_option_value` as option_value where option_value.option_id = an_alt_norm_form.value, '') 
) as data_index FROM `an_catalog_product_entity` ca_ent 

LEFT JOIN `an_catalog_product_entity_varchar` ca_var ON ca_var.entity_id = ca_ent.entity_id AND ca_var.attribute_id = 71 
LEFT JOIN `an_catalog_product_entity_text` ca_text ON ca_text.entity_id = ca_ent.entity_id AND ca_text.attribute_id = 72 
LEFT JOIN `an_catalog_product_entity_int` an_alt_norm_form ON an_alt_norm_form.entity_id = ca_ent.entity_id AND an_alt_norm_form.attribute_id = 306 ;

when i add 

ifnull(select option_value.value from `an_eav_attribute_option_value` as option_value where option_value.option_id = an_alt_norm_form.value, '')

they give me a syntax error

when i replace this line with simple ifnull(an_alt_norm_form.value, '') working fine what i m doing wrong

Upvotes: 0

Views: 63

Answers (2)

Sagar Gangwal
Sagar Gangwal

Reputation: 7937

INSERT IGNORE INTO an_catalogsearch_fulltext (product_id, store_id, data_index) 
SELECT DISTINCT ca_ent.entity_id as product_id, 
4 as store_id, 
CONCAT( 
ifnull(ca_var.value,''),' ',
ifnull(ca_ent.sku,''), ' ',
IFNULL((SELECT GROUP_CONCAT( ca_ent2.sku) FROM `an_catalog_product_entity` ca_ent2 LEFT JOIN `an_catalog_product_super_link` ca_sup 
        ON ca_sup.product_id = ca_ent2.entity_id WHERE ca_sup.parent_id = ca_ent.entity_id GROUP BY ca_sup.parent_id
),'')        ,' ', 
IFNULL((SELECT GROUP_CONCAT( ca_ent3.sku) FROM `an_catalog_product_entity` ca_ent3 LEFT JOIN `an_catalog_product_link` ca_lin 
        ON ca_lin.linked_product_id = ca_ent3.entity_id WHERE ca_lin.product_id = ca_ent.entity_id GROUP BY ca_lin.product_id),'')        ,' ', 

ifnull(ca_text.value,'') , ' ',
IFNULL((select option_value.value from `an_eav_attribute_option_value` as option_value where option_value.option_id = an_alt_norm_form.value),'')        ,' ') as data_index FROM `an_catalog_product_entity` ca_ent 

LEFT JOIN `an_catalog_product_entity_varchar` ca_var ON ca_var.entity_id = ca_ent.entity_id AND ca_var.attribute_id = 71 
LEFT JOIN `an_catalog_product_entity_text` ca_text ON ca_text.entity_id = ca_ent.entity_id AND ca_text.attribute_id = 72 
LEFT JOIN `an_catalog_product_entity_int` an_alt_norm_form ON an_alt_norm_form.entity_id = ca_ent.entity_id AND an_alt_norm_form.attribute_id = 306 ;

Try above query.

Upvotes: 1

C3roe
C3roe

Reputation: 96151

Compare the syntax you used to the already existing ifnull statements:

ifnull(
    (
        SELECT GROUP_CONCAT( ca_ent2.sku) FROM `an_catalog_product_entity` ca_ent2 LEFT JOIN `an_catalog_product_super_link` ca_sup 
        ON ca_sup.product_id = ca_ent2.entity_id WHERE ca_sup.parent_id = ca_ent.entity_id GROUP BY ca_sup.parent_id
    ),'')

here you wrapped the subquery in an additional set of parenthesis, but with your newly added 

ifnull(select option_value.value from `an_eav_attribute_option_value` ..., '')

those are missing.

Upvotes: 1

Related Questions