[FIXED] How to convert nested SQL to HQL

Issue

I am new to the Hibernate and HQL. I want to write an update query in HQL, whose SQL equivalent is as follows:

update patient set 
      `last_name` = "new_last", 
      `first_name` = "new_first" 
where id = (select doctor_id from doctor 
            where clinic_id = 22 and city = 'abc_city');

doctor_id is PK for doctor and is FK and PK in patient. There is one-to-one mapping.

The corresponding Java classes are Patient (with fields lastName, firstName, doctorId) and Doctor (with fields doctorId).

Can anyone please tell what will be the HQL equivalent of the above SQL query?

Thanks a lot.

Solution

String update = "update Patient p set p.last_name = :new_last, p.first_name = :new_first where p.id = some (select doctor.id from Doctor doctor where doctor.clinic_id = 22 and city = 'abc_city')";

You can work out how to phrase hql queries if you check the specification. You can find a section about subqueries there.

Answered By - mcyalcin
Answer Checked By - Senaida (JavaFixing Volunteer)