>>I have two tables. Field acno is common. In Table2 number
>>of records for acno may be many, but in table1 it is unique. table1 has a field remarks.
>>I want to delete all records for acno in table2 if it satisfies the condition:
>>
>>if table2.acno=table1.acno and remarks.table1='close'
>>
>>But I failed to do so. Please help.
>>
>>Chandan
>
>
>IF UPPER(ALLTRIM(Table1.Remarks )) == [CLOSING]
> DELETE FROM Table2 WHERE AcNo == Table1.AcNo
>ENDIF
>
If I'm reading this correctly, this matches only the current record in Table1. If you want to process all records in one statement you can
DELETE FROM Table2 WHERE AcNo in (SELECT acno FROM table1 WHERE remarks = "close")