Hi,

you didn't mentioned that you need to update these records but you've
wrote that you need to select these records.
Try to use this query then:

update A A
set A.TARGET = :NEW_TARGET_ID
where
(A.TARGET IS NULL) OR
(A.TARGET IN (SELECT ID FROM B B WHERE B.ID = A.TARGET AND B.VALUE <
:THRESHOLD))

or this procedure:

SET TERM ^;

CREATE PROCEDURE TEST
(
NEW_TARGET_ID INTEGER,
SOURCE_ID INTEGER,
THRESHOLD INTEGER
)
AS
DECLARE VARIABLE V_ID INTEGER;
BEGIN
FOR SELECT A.ID
FROM A A
LEFT JOIN B B
ON (B.ID = A.TARGET)
WHERE
(A.TARGET IS NULL) OR
(B.VALUE < :THRESHOLD)
INTO V_ID DO
UPDATE A
SET TARGET = :NEW_TARGET_ID
WHERE
ID = :V_ID;
END^

SET TERM ;^

Milan

I've wrote that SQL just in this email so it's not syntax-checked.

Dne 08.03.2010 12:13, korkless napsal(a):

>
> thanks for the reply, in my db the 2 columns Id are (NOT_NULL) PK, the
> nullable column is the FK Target, your query seems to get all the
> records where TARGET IS NOT NULL (b.Id is always not null).
>
> anyhow at the moment i cannot test becouse i have another problem, in
> my final procedure i need to update same records based on that select.
>
> this is my db
> Table A
> {
> INTEGER ID; //PK (NOT-NULL)
> INTEGER SOURCE: //NOT NULL FK TO ANOTHER TABLE
> INTEGER TARGET; //NULLABLE FK TO TABLE B
> }
>
> Table B
> {
> INTEGER ID; //PK (NOT-NULL)
> INTEGER VALUE; //NOT-NULL
> }
>
> i need to implement a procedure wich takes
> integer NEW_TARGET_ID
> integer SOURCE_ID
> integer THRESHOLD
>
> in this procedure i need to update the table A.
> what i need is to update all the records where A.SOURCE = :Source_ID
> and A.TARGET IS NULL or A.TARGET.VALUE < :threshold (pseudo code of
> the correct join).
> for that records i need to update TARGET to :NEW_TARGET_ID
>
> i'm tring to do samenthing as
>
> update A
> set A.TARGET = :NEW_TARGET_ID from A a left outer join B b....
>
> but this doesn't compile. how i can update records matched with ths
> type of select?
>
>

[Non-text portions of this message have been removed]