select to update mysql

Question

Select runs. I am trying to change the update but does not work. Who knows help me?

select
concat(
'Вы можете приобрести "', pd.name, '" с гарантией от производителя',
' "', max(case when ad.attribute_id=2 then ad.name end), '" ',
' "', max(case when ad.attribute_id=2 then pa.text end), '" ',
' и ',
' "', max(case when ad.attribute_id=3 then ad.name end), '" ',
' "', max(case when ad.attribute_id=3 then pa.text end), '" ')
from
oc_product_description pd
inner join oc_product_to_category pc on pd.product_id=pc.product_id
inner join oc_product_attribute pa on pd.product_id=pa.product_id and pd.language_id=pa.language_id
inner join oc_attribute_description ad on pa.attribute_id=ad.attribute_id and pa.language_id=ad.language_id
where 
pd.language_id=4
and pa.attribute_id in (2,3)
and pc.category_id=121
and pd.product_id = 102

my updates, i have error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by pd.name' at line 22

UPDATE `oc_product_description` pd
inner join oc_product_attribute pa on pd.product_id=pa.product_id and

pd.language_id=pa.language_id
inner join oc_attribute_description ad on pa.attribute_id=ad.attribute_id and

pa.language_id=ad.language_id
inner join oc_product_to_category pc on pc.product_id=pa.product_id

set pd.`description`= concat(
'Вы можете приобрести ', pd.name, ' с гарантией от производителя',
' ', max(case when ad.attribute_id=2 then ad.name end), ' ',
' ', max(case when ad.attribute_id=2 then pa.text end), ' ',
' и ',
' ', max(case when ad.attribute_id=3 then ad.name end), ' ',
' ', max(case when ad.attribute_id=3 then pa.text end), ' ')

where pd.language_id=4
and pa.attribute_id in (2,3)
and pc.category_id = 121
and pa.product_id = 102
group by pd.name

Try to add more details, since right now it is unclear what are you trying to achieve and what is your problem. Note that "does not work" can't be considered and clear problem description. — Andrey Korneyev, Jun 09 '15 at 10:05
Write your update query, there may be logical or syntax error in that. — Nikhil Batra, Jun 09 '15 at 10:13
I want to make a update of this select.how to do I do not know. You need to view my attempts? — Vadim Simonov, Jun 09 '15 at 10:13
I don't think you can update the join of two tables directly. Select query fetches data from multiple tables which is alright but update will not work like that. "Update" is intended to work on a single table only but you are updating the joins of multiple tables. — Nikhil Batra, Jun 09 '15 at 10:34

score 0 · Answer 1 · answered Jun 09 '15 at 13:00

i did it!

UPDATE oc_product_description pd
        inner join oc_product_to_category pc on pd.product_id=pc.product_id
        inner join oc_product_attribute pa on pd.product_id=pa.product_id and pd.language_id=pa.language_id
        SET pd.`description`=concat(
        'Вы можете приобрести "', pd.name,(
        select
        concat( '" с гарантией от производителя',
        ' "', max(case when ad2.attribute_id=2 then ad2.name end), '" ',
        ' "', max(case when ad2.attribute_id=2 then pa2.text end), '" ',
        ' и ',
        ' "', max(case when ad2.attribute_id=3 then ad2.name end), '" ',
        ' "', max(case when ad2.attribute_id=3 then pa2.text end), '" ')



    from oc_product_attribute pa2
    inner join oc_attribute_description ad2 on pa2.attribute_id=ad2.attribute_id and pa2.language_id=ad2.language_id

    where pa2.language_id=pd.language_id
    and pa2.attribute_id in (2,3)
    and pa2.product_id = pd.product_id
    ))
    where
    pd.language_id=4
    and pa.attribute_id in (2,3)
    and pc.category_id=121
    and pd.product_id = 102

select to update mysql

1 Answers1