Riho-Rene,

In a "good" dataset, the solution is to set your own custom LockSQL in the LockSQL property.

But this statement is..errrm...ambiguous all over..ambiguous refs in both the columns selected and the main where clause, et al. In short, it's the ambiguity that's the main cause of the error message, since it means IBO can't determine a column to use for the pessimistic lock. At best, this statement will get you unpredictable output...

The following SQL will get your dataset:

SELECT a.id,
a.account as a_account,
a.accountno
, f.ACCID
, f.STATUS
, a.BINFO
, a.BTAX
, f.IBANK
, f.IACCOUNT
, a.CREF
, a.CBANK
, b.BBANKNAME
, f.IBANKNAME
, f.CBANKNAME
FROM account a
join for_pay f on a.id=f.accid
where f.uid=:UID and f.install=:INST

/* for update */ not valid in a joined dataset

Since you started off with a.account in a sub-select, I'm guessing you wanted it to be a read-only column, hence the alias.

(Your statement didn't say where these fields were coming from, so these are guesses, but you get the picture).

You will have to set a KeyRelation for the update (an update statement can only update one table) and exact keylinks if you are going to get Pess Locking to work...but it won't work with just any joined dataset anyway. If the rows depend on the db_key for uniqueness, as is possibly the case here, a pess lock clause of 'set db_key=db_key' wouldn't work.

It would be clearer if we could tell whether you can resolve this to unique rows. If you can, then enter your own custom LockSQL to lock the row in the KeyRelation. But remember, if you are using a Stored Proc call to do UpdateSQL, you can't use Pess Locking at all.

rgds,
Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________