Hi!

At parent/children pattern, parent know children, but child don't know parent. I want to update a parent's children. I wrote below DML-style update statement:

update children c set c.name='test' where c.id in (select c.id from parent p join p.children c where p.name='parent');

It was translated as below mysql statement:

update children set name='test' where id in (select c.id from parent p inner join children c on p.id=c.parentid where p.name='parent');

But exception throwed when running. It is:

You can't specify target table 'children' for update in FROM clause.

I must do it using DML-style update because performence reason. How can I do?

Thanks very much!

OK. It isn't solved now and will not be solved by hibernate or mysql. Because hibernate's author think that is a bug of mysql and must solved by mysql. Other wise, mysql think it isn't a bug. Because they supply a more better way. That is:

update children c inner join (select c.id from parent p inner join children c on p.id=c.parentid where p.name='parent') a on c.id = a.id set name='test';

Of course, it is a good way to update multitable. But it isn't specified in standerer SQL.

I will use PostgreSQL to replace MySQL.

Thanks All.