hi carlos,
should work on PostgreSQL, which DELETE syntax doesn't support multiple tables, but allows using several tables in the WHERE clause:
DELETE FROM A WHERE A.bid = B.id AND B.type='foo'
is this the normal join syntax for postgresql ? how is table b referenced ?
if this is the ordinary syntax i could change the delete by query mechanism to support joined tables as well.
or in case of MySQL:
DELETE A FROM A, B WHERE A.bid = B.id AND B.type='foo'
OJB doesn't process the relationship and produces:
DELETE FROM A WHERE type='foo'
ojb currently does not even try to resolve references to joined tables in case of delete.
but the 'type' field doesn't exist in table A and hence the error.
I haven't tested with MySQL which supports the extended syntax including JOINS in the FROM clause. PostgreSQL is limited regarding DELETEs. OJB seems to implement the relations using JOINs in the FROM clause. INNER JOINS can be replaced by an AND condition in the WHERE clause, but it
the type of the join is defined in the platform class for each database. there's only one kind of join that can be defined.
jakob
seems OJB doesn't try to do that. Maybe that's the reason it doesn't work for PostgreSQL?
Carlos
Jakob Braeuchi wrote:
hi carlos,
sorry for the wrong answer :( ojb cannot delete from multiple tables !
actually i'm not sure if standard sql supports this kind of delete (mysql does afaik).
jakob
Jakob Braeuchi schrieb:
hi carlos,
afaik ojb should be able to delete objects by query, no matter whether it contains joins or not.
one thing to keep in mind: always clear the cache after execution of delete by query.
jakob
Carlos Villegas schrieb:
Hi,
Let's say I have reference on table A to a table B and I want to delete the A rows for which the related B instances match some criteria. Let's say my reference field is 'b'. The nice way to do it (if it worked) would be:
criteria.addEqualTo("b.type", "foo"); query = new QueryByCriteria(A.class, criteria); broker.deleteByQuery(query);
However, this doesn't work. I get the following SQL:
DELETE FROM A WHERE type='foo'
where I was expecting something like
DELETE FROM A WHERE A.bid = B.id AND B.type='foo'
in case of PostgreSQL but the syntax will depend on the database.
Should this work? Or is there any reason why this is not supported or not working now?
Carlos
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
