delete Restrictions

Restrictions for using delete

  • You cannot use delete with a multitable view (one whose from clause names more than one table), even though you may be able to use update or insert on that same view. Deleting a row through a multitable view changes multiple tables, which is not permitted. insert and update statements that affect only one base table of the view are permitted.

  • The SAP ASE server treats two different designations for the same table in a delete as two tables. For example, the following delete issued in pubs2 specifies discounts as two tables (discounts and pubs2..discounts):
    delete discounts
    from pubs2..discounts, pubs2..stores
    where pubs2..discounts.stor_id =
        pubs2..stores.stor_id

    In this case, the join does not include discounts, so the where condition remains true for every row; the SAP ASE server deletes all rows in discounts (which is not the desired result). To avoid this problem, use the same designation for a table throughout the statement.

  • If you are deleting a row from a table that is referenced from other tables via referential constraints, the SAP ASE server checks all the referencing tables before permitting the delete. If the row you are attempting to delete contains a primary key that is being used as a foreign key by one of the referencing tables, the delete is not allowed.