CODEWITHSUNDEEP
sqlh2
user2591854
user2591854

Reputation: 302

Table aliases in H2 SQL Delete

I'm attempting to implement closure table for a hierarchical group based on this

With H2, the following doesn't seem possible:

delete link
  from closure p, closure link, closure c
 where p.parent = link.parent and c.child = link.child
   and p.child=PARENT_ITEM    and c.parent=CHILD_ITEM

Can this be rewritten to work on H2 since it doesn't look like H2 supports table aliases in the DELETE statement based on the H2 grammar

Upvotes: 1

Views: 2656

Answers (1)

Thomas Mueller
Thomas Mueller

Reputation: 50097

Yes, a complete example is:

create table closure(parent int, child int);
insert into closure values(1, 10), (10, 100);

delete from closure where exists(
  select * from closure p, closure c
  where p.parent = closure.parent
  and c.child = closure.child
  and p.child=10
  and c.parent=1);

Upvotes: 1

Related Questions