--- In firebird-support@yahoogroups.com, Robin Davis <Ro.Davis@b...>
wrote:

>
> Hello folks,
>
> I have the following query in an Access 97 database front end:
>
> UPDATE [New Reworked Statements] INNER JOIN Invoices ON

(Invoices.Artist

> = [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
standards.

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)

Welcome

Adam