>>I got a feeling this isn't possible, but I thought I'd ask just in case. I want to do something like this:
>>
>>SELECT trips
>>DELETE ALL FOR trips.client_num NOT IN (SELECT client_num FROM clients)
>>
>>Basically, there's a lot of orphaned trips in the table and I want to clear them out. Is there any way to do that short of scanning through the trips table and looking up each client_num?
>>
>>Thanks,
>>
>Hi Michelle,
>
>A couple of additions to what Ken and Ed told you. If the table is part of a DBC and the database isn't current you'll need to use the Datebase!Table type syntax.
>
>The other thing worth mentioning is that unlike the DELETE command, DELETE - SQL uses record locking to mark multiple records for deletion. This means that you'll take a performance hit, unless you open the table exclusively. If that's not possible, in addition to the other solution, you can use the following to improve performance.

>SELECT Clients.Client_Num;
>  FROM Clients;
>  ORDER BY Clients.Client_Num;
>  INTO ARRAY a_clients
>SELECT Trips
>DELETE ALL FOR ASCAN(a_clients, Trips.Client_Num) = 0

>hth,

I was thinking of something like that, but I hadn't formed the idea into something substantial. I'm not too worried about performance. I only needed to do it once to clear out old data. Took about 20 seconds.

Thanks,

-Michelle