Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2004
    Posts
    6

    Unanswered: Delete across tables

    hi,

    I have two tables that are related, ie I created them with;

    create table cm_message (
    msgid varchar(40) not null primary key,
    location varchar(240),
    ts timestamp default 'now' not null,
    lastsent timestamp
    );

    create table cm_data (
    pkey integer not null primary key,
    subdata varchar(255),
    msgid varchar(40),
    foreign key (msgid) references cm_message(msgid)
    );

    basically for each entry in cm_message there can be several cm_data entries and they're linked using the msgid fields.

    I'm trying to write a purge script that will delete entries (in cm_message and cm_data) that have a cm_message.ts timestamp older than n hours. Can I do a 'delete from ... where cm_message.ts > n' which does some kind of union between the two tables and delete entries from both tables at one stroke?

    At the moment I'm looking at selecting all old entries from cm_message and deleting all in cm_data for each msgid, but there must be a more efficient way of using the relational stuff...

    thanks,
    nik

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Use the ON DELETE CASCADE option:
    Code:
    ...foreign key (msgid) references cm_message(msgid) ON DELETE CASCADE
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    which database system is this? because i don't know of any that will support this --
    Code:
    timestamp default 'now'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2004
    Posts
    6
    thanks LKBrwn_DBA.

    rudy, the database is firebird - I think it also accepts TODAY, TOMORROW and YESTERDAY which is nice and handy...

    nik

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    wow, ya learn sumpin new every day

    thanks nik
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2005
    Location
    Sweden
    Posts
    45

    Thumbs up Works in MySQL

    Hi,

    Does it work on a MySQL database?

    //M

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ManhQuynh
    Does it work on a MySQL database?
    the ON DELETE CASCADE? only for InnoDB tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •