mysql syntax error on sub query in insert values

Question

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

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'select option_value.value from an_eav_attribute_option_value as option_value w' at line 10 — user3337635
– user3337635, Commented Jul 20, 2017 at 10:55
In your previous ifnull statements that use a subquery, you wrapped that subquery in an additional set of (...) - why is that missing with this one then? — C3roe
– C3roe, Commented Jul 20, 2017 at 10:56
when i use subquery in ifnull they give main syntax error what should i do — user3337635
– user3337635, Commented Jul 20, 2017 at 10:58

C3roe · Accepted Answer · 2017-07-20 11:14:23Z

1

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.

answered Jul 20, 2017 at 11:14

C3roe

97.8k15 gold badges100 silver badges172 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

halfer · Accepted Answer · 2017-10-18 23:55:16Z

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.

Collectives™ on Stack Overflow

mysql syntax error on sub query in insert values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related