hibernate, mysql

Question

i have the following hql query:

UPDATE TaskAssessment taskAssessment 
SET taskAssessment.activeFlag = false 
WHERE taskAssessment IN 
(
SELECT taskAssessment2 
FROM TaskAssessment taskAssessment2 
Where taskAssessment2.activeFlag = true 
AND taskAssessment2.patient.id 
AND taskAssessment2.needsLevel.careNeed = :careNeed
)

but its giving me errors:

You can't specify target table 'TASK_ASSESSMENT' for update in FROM clause

could anyone help me to correct the query for mysql and hibernate. thanks in advance.

See http://stackoverflow.com/questions/45494/sql-delete-cant-specify-target-table-for-update-in-from-clause — axtavt, Feb 28 '11 at 17:25
i saw it, but when i try that it says unexpected ( in the query ! — Ikthiander, Feb 28 '11 at 17:27
i tried this: UPDATE TaskAssessment taskAssessment SET taskAssessment.activeFlag = false WHERE taskAssessment IN ( SELECT taskAssessment2 FROM (SELECT any FROM TaskAssessment any) taskAssessment2 Where taskAssessment2.activeFlag = true AND taskAssessment2.patient.id AND taskAssessment2.needsLevel.careNeed = :careNeed) — Ikthiander, Feb 28 '11 at 17:39

score 0 · Answer 1 · answered Feb 28 '11 at 17:37

0

To resolve You can't specify target table 'TASK_ASSESSMENT' for update in FROM clause, rewrite the query to use JOIN instead of IN (in mysql you need to write something like this):

UPDATE TaskAssessment a
INNER JOIN TaskAssessment a2 ON (a2.id = a.id)
SET a.activeFlag = 0  
WHERE a2.active_flag = 1 AND 
a2.patient_id = :patient_id AND a2.needsLevel_careNeed = :careNeed

answered Feb 28 '11 at 17:37

a1ex07

35,290
12
78
96

tried this: UPDATE TaskAssessment taskAssessment INNER JOIN TaskAssessment taskAssessment2 ON (taskAssessment.id = taskAssessment2.id) SET taskAssessment.activeFlag = false Where taskAssessment2.activeFlag = true AND taskAssessment2.patient.id = :patientId AND taskAssessment2.needsLevel.careNeed = :careNeed – Ikthiander Feb 28 '11 at 17:48
and it didnt work. gives error: expecting "set", found 'INNER' – Ikthiander Feb 28 '11 at 17:48
It's mysql syntax, not hql. To make it work you need to use `CreateSQLQuery` + `executeUpdate`. I'm not sure if you can use join + update in hql. – a1ex07 Feb 28 '11 at 18:20
1

if you kindly look at the heading it mentions hibernate and yes i am working with hql, so i need help with hql rather than just simple mysql query. – Ikthiander Mar 01 '11 at 09:09

hibernate, mysql

1 Answers1