CODEWITHSUNDEEP
mysqlormcoldfusionhql
crikos
crikos

Reputation: 41

Coldfusion ORM update with Join

I am looking for help on a HQL-statement, where I would like to do an update with a JOIN. I tried following statement, which runs fine in MySQL-Workbench:

UPDATE table1 t1 
SET    t1.status='Running' 
JOIN   t1.table2 t2  
WHERE  t1.status='Open' AND t2.destination ='mydestination' "

but it gives the error:

expecting "set", found 'JOIN' near line 1, column 16

Upvotes: 1

Views: 108

Answers (1)

John Whish
John Whish

Reputation: 3036

It seems to me that as this is a bulk update operation then you should be using SQL for this not HQL. ORM is not really designed for this type of update. You can always run the SQL statement and then if you need to, load the object graph (your ORM entities) after.

As for the SQL you'll need to run it using cfquery (or the cfscript equivalent), from your HQL example it'd look something like this as SQL (assuming MySQL as you mention MySQL Workbench)

<cfquery>
    UPDATE table1 t1 
    INNER JOIN table2 t2 ON t1.col = t2.col
    SET status='Running' 
    WHERE status='Open' 
        AND t2.destination='mydestination'
</cfquery>

If you want to do it with HQL then you usually need to use a subquery. Something like this:

update table1 t1 
set t1.status = 'Running' 
where t1.state = 'Open' 
and t1 in (
    select t2.table1
    from table2 t2
    where t2.destination='mydestination'
)

Upvotes: 2

Related Questions