Foxhound is the better* Database Monitor for SQL Anywhere. *better: More thorough, more relevant, more effective. ...more Alerts, more All Clears, more details, more control in your hands.

DELETE FROM FROM

How do I qualify a SQL delete statement with a where condition involving columns from a second table? We want to clean up the sales prospect table by deleting all rows that have been copied to the customer table.

With Watcom SQL 4 and many other database systems only one table can be directly named in the delete statement. To refer to a second table it is necessary to employ a subquery like this correlated subselect:

   delete from prospect
    where exists (
   select customer.customer_id
     from customer
    where customer.customer_id = prospect.prospect_id );

In Sybase SQL Anywhere 5 a second from clause has been added to the delete statement. The first from clause names the table from which rows are to be deleted while the second (optional) from clause names tables that can be referred to in the where clause:

   delete from prospect
     from prospect, customer
    where prospect.prospect_id = customer.customer_id;