delete from multple tables

Tea_J

Hi guys

I was wondering.. ya'll got a method to delete rows from multiple tables?

i am making a DELETE USER PROFILE routine.. i normally delete all rows from each table that has the user's primary id each on its own separate SQL query..

is there a way to do this in ONE query?

tea

wilku

Just look at mysql documentation for DELETE statement:

Multiple-table syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
tbl_name[.] [, tbl_name[.] ...]
FROM table_references
[WHERE where_definition]

Tea_J

Hi,

thanks

i cant seem to get this right..

i have 3 tables:

applang, edu, portfolio

i need to delete all rows that are related to a user with aid=24

DELETE applang.*, edu.*, portfolio.* FROM applang, edu, portfolio WHERE aid='24'

im getting an "ambigous" error on aid

tea

mrhappiness

you have to tell mysql which column it has to use

WHERE
  applang.aid = edu.aid
  AND edu.aid = portfolio.aid
  AND portfolio.aid=24

Tea_J

ahh.... yes ofcourse. thanks.. i got it now..

Tea_J

hmm.. i can't seem to make this work.. what am i missing here?

//Multiple Delete tables
  		$myquery="DELETE apps , applang , edu , portfolio 
  		FROM  apps, applang, edu, portfolio
  		WHERE
  		applang.aid = edu.aid
  		AND edu.aid = portfolio.aid
  		AND portfolio.aid = apps.[b]ndex[/b]
  		AND apps.ndex = '$aid'
  		";

note on the bold index.. my reference to it is a different index called "ndex"..

I dont get any errors.. but my rows are still there..

also tried

//Multiple Delete tables
  		$myquery="DELETE apps , applang , edu , portfolio 
  		FROM  apps, applang, edu, portfolio
  		WHERE
  		applang.aid = '$aid'
  		AND edu.aid = '$aid'
  		AND portfolio.aid = '$aid'
  		AND apps.ndex = '$aid'
  		";

doesnt work either.. it looks like it should work..

what am i missing here?
tea

searain

//Multiple Delete tables
$myquery="DELETE apps, applang, edu, portfolio FROM apps, applang, edu, portfolio
WHERE
applang.aid = '$aid'
AND edu.aid = '$aid'
AND portfolio.aid = '$aid'
AND apps.ndex = '$aid'
";

Is this better than run a simple delete for each table?

Tea_J

Is this better than run a simple delete for each table?

well i always thought that its better to execute as much as you can in ONE mysql query than have several queries seperately connecting and doing transactions on the database.... i could be wrong.. again..

tea