Update From Syntax

[Harry Leboeuf]

Hello,

This is a syntax we where using on a sql-server, or the syntax how we would write it. As our company Joomla intranet works on MySql i'm not familar with the syntax here ... What should i change, i've tried different version and each time running into a error.

update jos_scouts_rsform_submission_values set sv.fieldvalue =
((select sum(case when sv2.fieldvalue = 1 then 1 else 0 end) from jos_scouts_rsform_submission_values sv2 where sv2.submissionid = sv.submissionid and sv2.fieldname like 'menu%')*19 )+
((select sum(case when sv2.fieldvalue = 2 then 1 else 0 end) from jos_scouts_rsform_submission_values sv2 where sv2.submissionid = sv.submissionid and sv2.fieldname like 'menu%')*13 )+
((select sum(case when sv2.fieldvalue = 3 then 1 else 0 end) from jos_scouts_rsform_submission_values sv2 where sv2.submissionid = sv.submissionid and sv2.fieldname like 'menu%')*11 )+
((select sum(case when sv2.fieldvalue = 4 then 1 else 0 end) from jos_scouts_rsform_submission_values sv2 where sv2.submissionid = sv.submissionid and sv2.fieldname like 'menu%')*8 )+
((select sum(case when sv2.fieldvalue = 5 then 1 else 0 end) from jos_scouts_rsform_submission_values sv2 where sv2.submissionid = sv.submissionid and sv2.fieldname like 'menu%')*10 )+
((select sum(case when sv2.fieldvalue = 6 then 1 else 0 end) from jos_scouts_rsform_submission_values sv2 where sv2.submissionid = sv.submissionid and sv2.fieldname like 'menu%')*7 )
from jos_scouts_rsform_submission_values sv
where sv.formid = 5
and sv.fieldname = 'result'

I know its a strange query but this is how our component stores its info.

Kind regards, Harry