From SQL to HQL Join and Delete

Question

Have this SQL query working like a charm, would like to convert it to HQL but have some difficulties.

SQL Query:

DELETE med1
FROM MEDECINES med1, MEDECINES med2
WHERE (
    REPLACE(med1.`MEDECINE_ID`, ' ', '') < REPLACE(med2.`MEDECINE_ID`, ' ', '') AND 
    REPLACE(med1.`MEDECINE_DCI`, ' ', '') = REPLACE(med2.`MEDECINE_DCI`, ' ', '') AND 
    REPLACE(med1.`MEDECINE_MARK`, ' ', '') = REPLACE(med2.`MEDECINE_MARK`, ' ', '') AND 
    REPLACE(med1.`MEDECINE_FORM`, ' ', '') = REPLACE(med2.`MEDECINE_FORM`, ' ', '') AND 
    REPLACE(med1.`MEDECINE_DOSAGE`, ' ', '') = REPLACE(med2.`MEDECINE_DOSAGE`, ' ', '') AND 
    med1.`MEDECINE_REDEEMABILITY` = med2.`MEDECINE_REDEEMABILITY`
);

HQL (what i was able to write till now):

DELETE FROM Medecine med1
        WHERE EXISTS (
           FROM Medecine med2
           WHERE(
                med1.medecineId < med2.medecineId AND
                REPLACE(med1.medecineDci, ' ', '') = REPLACE(med2.medecineDci, ' ', '') AND
                REPLACE(med1.medecineMark, ' ', '') = REPLACE(med2.medecineMark, ' ', '') AND
                REPLACE(med1.medecineForm, ' ', '') = REPLACE(med2.medecineForm, ' ', '') AND
                REPLACE(med1.medecineDosage, ' ', '') = REPLACE(med2.medecineDosage, ' ', '') AND
                med1.medecineRedeemability = med2.medecineRedeemability
            )
        )

score 0 · Answer 1 · answered Mar 11 '18 at 06:31

0

Use EXISTS

DELETE FROM medecines med1
WHERE  EXISTS (SELECT 1
               FROM   medecines med2
               WHERE  REPLACE(med1.medecine_id, ' ', '') <
                      REPLACE(med2.medecine_id, ' ', '')
                      AND REPLACE(med1.medecine_dci, ' ', '') =
                          REPLACE(med2.medecine_dci, ' ', '')
                      AND REPLACE(med1.medecine_mark, ' ', '') =
                          REPLACE(med2.medecine_mark, ' ', '')
                      AND REPLACE(med1.medecine_form, ' ', '') =
                          REPLACE(med2.medecine_form, ' ', '')
                      AND REPLACE(med1.medecine_dosage, ' ', '') =
                          REPLACE(med2.medecine_dosage, ' ', '')
                      AND med1.medecine_redeemability =
                          med2.medecine_redeemability );

answered Mar 11 '18 at 06:31

Kaushik Nayak

30,772
5
32
45

Did as u suggested, here's the output: java.sql.SQLException: Table 'MEDECINES' is specified twice, both as a target for 'DELETE' and as a separate source for data, sorry for late answer – Mssm Mar 14 '18 at 19:17
@Mouley : try after removing `select 1` in your final query. – Kaushik Nayak Mar 15 '18 at 01:28
sorry didn't get you, you mean i have to write two queries or ... ? – Mssm Mar 15 '18 at 06:15
@Mouley : I mean to say you start the sub-query after exists as "FROM medecines" : Check this https://stackoverflow.com/questions/3672444/where-exists-in-hibernate-hql – Kaushik Nayak Mar 15 '18 at 06:24
i edited the topic to post my new HQL, check it please – Mssm Mar 17 '18 at 13:54
@Mouley I'm sorry I won't be able to help u as I don't have the required environment to test. You may wait for others to answer or ask a new question – Kaushik Nayak Mar 17 '18 at 14:25

From SQL to HQL Join and Delete

1 Answers1