Multi-table deletes
My SQL: delete c, gt, g, sp from Companies as c, GroupTypes as gt, Groups as g, ServicePersons as sp where gt.companyuid = c.uid and g.companyuid = c.uid and sp.companyuid = c.uid and c.id = '01' This works fine as long as there are records in all tables, but if one of the tables doesn't have any records then the whole delete won't delete any records. I'm trying to clean up all records related to a specific company. Would it be better to do something like this with triggers? Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multi-table deletes
I changed my tables to use foreign key constraints with on delete cascade and that did what I needed. Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -Original Message- From: David Ruggles [mailto:[EMAIL PROTECTED] Sent: Friday, July 18, 2008 11:51 AM To: 'mysql' Subject: Multi-table deletes My SQL: delete c, gt, g, sp from Companies as c, GroupTypes as gt, Groups as g, ServicePersons as sp where gt.companyuid = c.uid and g.companyuid = c.uid and sp.companyuid = c.uid and c.id = '01' This works fine as long as there are records in all tables, but if one of the tables doesn't have any records then the whole delete won't delete any records. I'm trying to clean up all records related to a specific company. Would it be better to do something like this with triggers? Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.138 / Virus Database: 270.5.1/1560 - Release Date: 7/18/2008 6:47 AM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi-table deletes
Bhavin, Saturday, June 15, 2002, 2:36:07 AM, you wrote: BV Just wanted to make sure before I proceed to write a script, BV Muti-table deletes as such: BV delete from Keyname_Keyvalue,Keyvalue WHERE BV Keyvalue.KeyvalueKey=Keyname_Keyvalue.KeyvalueKey AND BV Keyname_Keyvalue.KeynameKey='100'; BV ERROR 1064: You have an error in your SQL syntax near 'Keyvalue WHERE BV Keyvalue.KeyvalueKey=Keyname_Keyvalue.KeyvalueKey AND Keyname_Key' at line 1 BV Are not supported in 3.23 but supported in 4.x. Is that correct? Any other BV options besides writing a script? You used the second multi-table delete format. It will be supported in 4.0.2: http://www.mysql.com/doc/D/E/DELETE.html BV Regards, BV Bhavin Vyas. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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
Multi-table deletes
Just wanted to make sure before I proceed to write a script, Muti-table deletes as such: delete from Keyname_Keyvalue,Keyvalue WHERE Keyvalue.KeyvalueKey=Keyname_Keyvalue.KeyvalueKey AND Keyname_Keyvalue.KeynameKey='100'; ERROR 1064: You have an error in your SQL syntax near 'Keyvalue WHERE Keyvalue.KeyvalueKey=Keyname_Keyvalue.KeyvalueKey AND Keyname_Key' at line 1 Are not supported in 3.23 but supported in 4.x. Is that correct? Any other options besides writing a script? Regards, Bhavin Vyas. - 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
