I guess I am a bit confused at the response below... Please see:
http://www.mysql.com/doc/en/ANSI_diff_Foreign_Keys.html Why can't the foreign key logic be used in version 3.23?...... At 09:45 AM 1/1/03 +1100, Greg Matthews wrote:
Didn't fully read your question. Need the extra bit in the subselect to identify air force rows only, but again, this won't work until 4.1 with subselects is released. delete from tids where exists ( select 1 from tids_admin where tids_admin.id = tids.admin_id and tids_admin.service = 'Air Force') ----- Original Message ----- From: "Greg Matthews" <[EMAIL PROTECTED]> To: "Richard Forgo" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, January 01, 2003 9:29 AM Subject: Re: simple (I thought) delete question > if you want to do it in one statement, you'll need to wait for subselects to > be implemented in MySql. Apparently this is underway and was scheduled to > appear in MySql 4.1 > > if subselects were supported, you'd do something like this: > > delete from tids where exists ( select 1 from tids_admin where tids_admin.id > = tids.admin_id) > > otherwise, you'll need to issue multiple statements. > > e.g. > 1. locate records to delete > 2. delete records either in bulk using an IN (id1, id2, id3,....) expression > or just deleting one at a time > > greg. > > ----- Original Message ----- > From: "Richard Forgo" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Wednesday, January 01, 2003 8:12 AM > Subject: simple (I thought) delete question > > > > Hi folks, > > > > I hope that dumb questions are allowed in here ... > > > > I have two MySQL tables and I would like to delete records from one > > based on associated values in another. For example, using the example > > tables below, how would I delete all the records in the TIDS table that > > had an associative value of 'Air Force' in the TID_ADMIN table? > > > > > > TID_ADMIN > > -------------------- > > ID (pk) | Service > > -------------------- > > > > 1 Army > > 2 Navy > > 3 Air Force > > > > > > TIDS > > -------------------------------------- > > ID | ADMIN_ID | Project Name > > -------------------------------------- > > > > 1 1 Project X > > 2 1 Project Y > > 3 2 Vision C > > 4 3 Clearout T > > 5 3 Clearout F > > 6 3 Trustee 433 > > > > I have tried, to no avail, using variants of ... > > > > DELETE FROM tids, tid_admin > > WHERE tid_admin.admin_id = tids.admin_id > > AND tid_admin.service = 'Air Force' > > > > Can someone point me in the right direction. I'm still feeling my way > > through all this. > > > > > > Rik Forgo > > JIST3 > > Army Test, Training and Technology Integration Office (T3I) > > Diverse Technologies Corp. > > (c) 443.463.8571 > > (h) 410.859.8474 > > > > > > > > > > --------------------------------------------------------------------- > > Before posting, please check: > > http://www.mysql.com/manual.php (the manual) > > http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
--------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
