[Wylug-help] OT: SQL Help

[Roger Leigh]

Smylers <Smylers at stripey.com> writes:

> 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').

Hi Simon,


A small bit of advocacy: if you were using PostgreSQL you could
completely automate this using constraints:

Since items belong to an order, you would do something like this:

CREATE TABLE orders (
       id serial PRIMARY KEY,
       ...
       );

CREATE TABLE items
       order_id int NOT NULL REFERENCES orders CONSTRAINT item_order ON DELETE CASCADE;
       ...
       );

Now, when you delete an order, the cascading deletion will remove all
items which reference that order id, in order to preserve referential
integrity.  So, zero effort on your part to clean up, which is how it
should be!


Another possibility would be to use a stored procedure, which gets
invoked whenever a certain action takes place:

CREATE OR REPLACE FUNCTION cleanup_orders RETURNS TRIGGER AS '
  DECLARE
  BEGIN
    -- remove all items from the order, prior to the order removal
    DELETE FROM items WHERE (order_id = OLD.id);
    RETURN OLD;
  END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER cleanup_orders
       BEFORE DELETE
       ON orders
       FOR EACH ROW
       EXECUTE PROCEDURE cleanup_orders();

This is slightly more flexible--you could do whatever you wanted in
the procedure.  In this case, it's effectively the same as the
cascading deletion.  You can use triggers like this to do strict
validity checking on data being inserted or updated--very useful to
ensure your data can never be incorrect.


What's nice about both approaches is that all the work takes place
within a tranaction block, even though to the user it's just a single
statement.  If the trigger encounters and error, it can

  RAISE EXCEPTION ''Error message'';

and the transaction will abort, and the user will be notified of the
error.  The database will be in exactly the same state as before you
started--no partially deleted order.


--
Roger Leigh

                Printing on GNU/Linux?  http://gimp-print.sourceforge.net/
                GPG Public Key: 0x25BFB848.  Please sign and encrypt your mail.