[eclipselink-users] UPDATE query with joined inheritance: doesn't work o

[Laird Nelson <ljnelson@xxxxxxxxx>]

I started out thinking this was an issue with Informix.

Now I'm thinking that maybe there could be something in EclipseLink.

Briefly we have an entity subtype (Foo; inherits some superclass entity using table-per-class inheritance).  We want to set its "x" attribute to a particular value.

The JPQL is (simply):

UPDATE Foo f SET f.x = 0 WHERE f.x = :invoiceId

EclipseLink outputs the following SQL:

UPDATE foo_subclass SET x = ?
 WHERE EXISTS(SELECT t0.id
                FROM foo_superclass t0, foo_subclass t1
               WHERE ((t1.x = ?) AND ((t1.id = t0.id) AND (t0.DTYPE = ?)))

The problem is that Informix has a list of conditions that have to be met for subqueries in a WHERE clause (http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.sqls.doc%2Fids_sqs_2005.htm).  This SQL appears to violate them, but I can't really see how.  It looks good and sensible.

From poking around at other vendors' documentation, I suspect this might violate their conditions as well.  MySQL I know from past experience has some restrictions on subqueries in UPDATE WHERE clauses; I don't know offhand if this would break there as well.

Is this pilot error?  Is there something else in the JPQL we should be doing in an inherited entity situation?

The StackOverflow question (tagged EclipseLink) is here in case that is also useful: http://stackoverflow.com/questions/15123285/how-can-i-get-eclipselink-to-output-valid-informix-sql-for-an-update-where-claus

Thanks,

Best,

Laird

--
http://about.me/lairdnelson