[Wylug-help] OT: SQL Help

[Aaron Crane]

Smylers writes:
> I don't think there's any standard SQL syntax that permits deletion from
> multiple tables in one go.

Not directly, as far as I know, but there are (at least) two ways of doing
this in standard SQL.  The first option is to use two deletions, but in a
transaction, so that all other transactions see the effects of both or
neither:

  BEGIN;
  DELETE FROM items WHERE order_id IN (SELECT ...);
  DELETE FROM orders WHERE ...;
  COMMIT;

For some table types (InnoDB and BDB, I think), this is also an option in
MySQL from 3.23.17.  If you're using MySQL 3 with a non-transactional table
type, you ought to lock the relevant tables when doing such paired
deletions.  (In MySQL 4, cross-table deletion is much easier than table
locking.)

The other option (and the one I prefer) is to use foreign key constraints to
get the 'other' deletion to happen automatically:

  CREATE TABLE orders (
    order_id int not null primary key,
    ...
  );

  CREATE TABLE items (
    item_id int not null primary key,
    order_id int not null REFERENCES orders
      -- If an order gets deleted, also delete any rows in this table
      -- which refer to it:
      ON DELETE CASCADE,
    qty int not null default 1 CHECK (qty >= 1),
    product_id int not null REFERENCES products
  );

  -- This automatically deletes any items which are part of a deleted order:
  DELETE FROM orders WHERE ...;

Apparently this works in MySQL from 3.23.44 onwards if you use InnoDB
tables, but I've never tried it.

--
Aaron Crane