[eclipselink-users] Creation of temporary tables for MySQL database upda

[<ravikirann@xxxxxxxxxxx>]

Hi,

 

   I have a onetomany bidirectional relationship between Customer and Order beans(each customer has multiple orders).

I get an unexpected sql queries generated from a JPQL query in Eclipselink 1.1.1 for MySQL db only.

 

 

My JPQL query,

 

update orders_bidirectional o set o.name = 'ravi' where o.customer.id = :custid

 

 

 

SQL query generated for Oracle db,

 

UPDATE orders_bidirectional SET NAME = ? WHERE EXISTS(SELECT t1.ID FROM customer_bidirectional t0, orders_bidirectional t1 WHERE ((t0.ID = ?) AND (t0.ID = t1.CUSTOMER_ID)) AND t1.ID = orders_bidirectional.ID)

 

 

 

SQL queries generated for MySQL db,

 

CREATE TEMPORARY TABLE IF NOT EXISTS TL_orders_bidirectional LIKE orders_bidirectional
INSERT INTO TL_orders_bidirectional (ID, NAME) SELECT t1.ID, ? FROM customer_bidirectional t0, orders_bidirectional t1 WHERE ((t0.ID = ?) AND (t0.ID = t1.CUSTOMER_ID))
UPDATE orders_bidirectional, TL_orders_bidirectional SET orders_bidirectional.NAME = TL_orders_bidirectional.NAME WHERE orders_bidirectional.ID = TL_orders_bidirectional.ID
DELETE FROM TL_orders_bidirectional

 

 

Like for other databases, is it possibe to use update all query for MySQL with a single sql query. Generating so many sql queries is

scalability issue for us.

 

 

regards,

ravi.