[Wylug-help] OT: SQL Help

[Smylers]

James Holden (Wylug) writes:

> I'm strugging to create a SQL query to tidy up the database on on online
> store I manage. Homebrewed application and all that. I want to delete
> some abandoned baskets from the database, but the data is stored in two
> separate tables.
>
> Can anyone help with the query to do this?

I don't think there's any standard SQL syntax that permits deletion from
multiple tables in one go.  You don't mention which DBMS you're using.
If it's 'MySQL 4' then you can do:

  DELETE
  FROM orders, items
  USING orders, items
  WHERE items.order_id = orders.order_id
    AND creation < 1050561000

(replacing the last test there with however you determine that an order
is 'too old').

> Could I do two queries, one to delete the dud lines from orders, and
> one to delete the orphaned lines in 'items'?

Yes, and I think this is the only way with standard SQL.  Also, t'other
way round, you could delete the items first.  The easiest way of doing
either of these involves subselects.  Deleting the orders first:

  DELETE
  FROM orders
  WHERE creation < 1050561000

  DELETE
  FROM items
  WHERE order_id NOT IN
  (
    SELECT order_id
    FROM orders
  )

Deleting the items first:

  DELETE
  FROM items
  WHERE order_id IN
  (
    SELECT order_id
    FROM orders
    WHERE creation < 1050561000
  )

  DELETE
  FROM orders
  WHERE creation < 1050561000

If you're using 'MySQL 3' then you have neither multiple-table DELETE
nor subselects available.  For SELECT queries its often possible to
derive subselect-equivalents using LEFT JOIN and IS NULL, but JOIN
doesn't work with DELETE.

So probably the only way of achieving this is to fake the subselect by
doing the 'inner' query first then using its results to composing the
'outer' query, by populating IN (...) with what that query believes to
be constants.

Smylers