CREATE TABLE acmast (accode n(6), acname c(50),opamt n(10,2)) >SELECT acmast INSERT INTO acmast (accode,acname,opamt) VALUES (1, 'party name', 54200) CREATE TABLE sales (idno n(6), billno c(6), billdt d, accode n(6), qty n(7,2), amt n(10,2)) INSERT INTO sales (idno, billno , billdt , accode , qty , amt ) ; VALUES (1, 'aa/101', DATE(2011,4,04),1,22,509) INSERT INTO sales (idno, billno , billdt , accode , qty , amt ) ; VALUES (1, 'aa/102', DATE(2011,4,14),1,28,852) INSERT INTO sales (idno, billno , billdt , accode , qty , amt ) ; VALUES (1, 'aa/103', DATE(2011,4,24),1,18,654) > SELECT sales.billno,sales.billdt, sales.accode, acmast.acname, acmast.opamt, sales.qty, sales.amt, cast(0 as I) as idTemp ; FROM sales ; LEFT JOIN acmast ON sales.accode=acmast.accode INTO CURSOR tempcurs READWRITE update tempcurs set idTemp = recno() select acname, min(idTemp) from tempCurs into filtCurs group by 1 update tempcurs set opamt = 0 where idTemp not in ; Select idTemp from FiltCursdepending on the backend first selecting opamt as 0 and then setting only 1 line might be faster even if the update statement involves one more join. experiment ;-)