>Is it possible to delete from multiple tables with one query?
I.E., if a
>program is removed from our "programs" table, any row with
that program_id
>should be removed from various other tables:
>
>DELETE FROM programs, stats, comments WHERE program_id = 5
>
>I'm getting an error when trying to run that query, do I need
to use three
>separate queries?
Yes sir.
Bob Hall
That's the response I got from the New Riders book on MySQL as well.
"Just use multiple delete statements".
The problem is, if you need to delete rows from more than one table,
and the key to finding which rows to delete is a join, you're kind of
hung, aren't you.
For example, this is what I would LIKE to do:
DELETE FROM refCounter,referer WHERE refCounter.refKey=referer.refKey
AND referer.URL='';
In looking through the archives, I saw that a MySQL developer
mentioned this kind of delete statement as an upcoming feature of a
still-in-development version of MySQL.
So do I wait, or does someone out there know a way in which I can
translate my SQL needs into two delete statements while still only
deleting the rows I want, and not leaving a bunch of linked rows
undeleted?
My hunch is that there must be some way to delete the one table, then
do a delete from the other table via a join that looks for null
values?
Or, perhaps I have to create a temporary join table which I can use
as a master list for which key values to delete.
These are pretty "dirty" ways to get the job done, when it could be
so simple. Am I overlooking something obvious?
Kristofer Widholm
---------------------------------------------------------------------
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
