How do I modify a delete sql statement in H2

[gabjos]

I have a delete statement that is giving me errors in H2. How do I
modify it for H2?

Here it is:

DELETE FROM multi_pages pages INNER JOIN bib_holdings bh ON bh.id =
multi_pages.id WHERE bh.pdf_name = 'ak-1'

[gabjos]

On Apr 7, 2:02 pm, gabjos <sikyala_raq...@bah.com> wrote:
> I have a delete statement that is giving me errors in H2. How do I
> modify it for H2?
>
> Here it is:
>

> DELETE FROM multi_pages INNER JOIN bib_holdings bh ON bh.id =

> multi_pages.id WHERE bh.pdf_name = 'ak-1'

I only know Oracle commands and in Oracle it would be like

delete from multi_pages where bib_holdings.id=multi_pages.bh_id and
pdf_name='ak-1';

[Maaartin]

Rows from which table do you want to delete? In MySql you'd need to
write something like

DELETE t1 FROM t1 JOIN t2 ...

but this is actually an updatable view and AFAIK doesn't work with H2.
So you probably need something like

DELETE FROM multi_pages pages WHERE EXISTS (SELECT 0 FROM bib_holdings
bh WHERE bh.id =
multi_pages.id AND bh.pdf_name = 'ak-1')

[Thomas Mueller]

Hi,

There is a feature request for this kind of "delete" statement, and I
will increase the priority, but it still is relatively low priority:

MySQL compatibility: multi-table delete: DELETE .. FROM .. [,...]
USING - See http://dev.mysql.com/doc/refman/5.0/en/delete.html

The disadvantage of this syntax is that it's not compatible with other
databases. So I suggest to use a single table delete, and "where
exists(...)" as Maaartin suggested.

Regards,
Thomas