Hi,
I'm not trying to delete from multiple tables, just from one table but using a query that uses several related tables. The example I gave 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'
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'
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 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]
