Subject Re: Why does this query fail with Firebird 1.5.2 but not with Firebird 2?
Author Adam
--- In, Robin Davis <Ro.Davis@b...>
> Hello folks,
> I have the following query in an Access 97 database front end:
> UPDATE [New Reworked Statements] INNER JOIN Invoices ON
> = [New Reworked Statements].Artist) AND ([New Reworked
> Statements].InvoiceID = Invoices.InvoiceID) SET Invoices.[Statement
> Sent] = Date();
> If I use the current stable release, or the 1.53 rc releases of
> Firebird, the above seems to choke the system; stops it dead in its
> tracks; and I have to force Access closed and start again.

Hi Robin,

I was trying to figure out the placeholders then I remembered that
Access allows spaces if you use [], gotta love adherance to your own

Apart from the keywords and spaces in table names that might give you
some grief, I didn't think (but might be wrong) that join was a
supported operation in the update yet (except obviously in a
subselect). Maybe it is new to FB 2.

You could probably convert this to something like

update invoices i
set statement_sent = 'today'
where exists (
select 1
from NewReworkedStatements n
where i.Artist = n.Artist AND n.InvoiceID = i.InvoiceID

Disclaimer: Haven't tested this or checked it, but I think it should
do what you need

If that fails, you can create a stored procedure that has a for select
loop that selects the invoice id of the records to update (using your
join logic), then inside the loop run a delete statement on that
particular id.

> (Very new at all this)