sql - MySQL Doesn't Run in SqLite Can I Build it with Doctrine2 Query Builder? -

i have nice query updating "parent" (observation) value depending on set "child" (action) table. observation can have many actions, , when marked 'resolved' parent observation marked resolved too.

this mysql script.

update observation     inner join      (         select              co.id obs_id,             sum(at.resolved) tasks_resolved,             count(at.id) total_tasks          observation co          inner join action_task @ on at.observation_id = co.id         co.id = (             select task.observation_id action_task task             task.id = 5         )     ) val on val.obs_id = observation.id     set observation.resolved = case         when val.tasks_resolved=val.total_tasks 1         else 0     end ; 

i wanted bring query doctrine 2 repository. tried afternoon build query builder script it, ended running whole script exec command.

i started building functional tests, threw error because cannot have inner join in update - because inner join allowed in mysql.

so updated query remove inner join on update. looks this:

update observation, (         select             sum(at.resolved) tasks_resolved,             count(at.id) total_tasks          observation co          inner join action_task @ on at.observation_id = co.id         co.id = (             select task.observation_id action_task task             task.id = 5         )     ) val     set observation.resolved = case         when val.tasks_resolved=val.total_tasks 1         else 0     end     observation.id = (         select task.observation_id action_task task         task.id = 5     ) ; 

i facing problem sql runs fine when used within site, fails when trying run functional tests. throwing error:

pdoexception: sqlstate[hy000]: general error: 1 near ",": syntax error

the normal site running on mysql , functional tests run using sqlite. think save more stuffing around build query query builder , save cross-database issues.

i wondering if there way write query query builder?

that comma still join operator (or if allowed in update).

the update statement allows single table. have lookups correlated subqueries:

update observation set resolved = (select sum(resolved) = count(id)                 action_task                 observation_id = observation.id) id = (select observation_id             action_task             id = 5)