[SOLVED] DELETE JOIN

[jaymc]

I have just ventured into using JOINS for mass row deletion accross tables

 

However, i notice that adding a limit (LIMIT 5) for example renders the query useless

 

Without the limit its fine, but takes longer because it has to carry on after it deletes 5 rows

 

Is this just the way it works, becasuse Id rather add a limit when I know for certain Im only deleting 5 rows, 1 rows in each of the 5 tables for instance

[aschk]

Your limit would surely be 1 then not 5...

imagine the reverse SELECT statement, you'd be pulling back information with 1 row only. I'm sure fenway will correct me if i'm wrong on that.

[fenway]

That depends on the relationship between the tables... but yes, if 1-to-1, then limit 1, not limit 5.

[jaymc]

Same issue with JOIN for an UPDATE, it doesnt like having LIMIT in there, here is an example, works without UPDATE but not with.

 

$q = "UPDATE members m

INNER JOIN members_info mi ON m.username = mi.username

SET

fullname = '$fullname',

loc = '$location',

nightclub = '$nightclub',

footyteam = '$football',

relationship = '$relationship',

profession = '$profession',

favmusic = '$music',

sexuality = '$sexuality',

mi.info = '$message'

WHERE m.username = '$User_Session'

LIMIT 1";

[mwasif]

Try this statement

$q = "UPDATE members, members_info 
   SET
   fullname    = '$fullname',
   loc       = '$location',
   nightclub    = '$nightclub',
   footyteam    = '$football',
   relationship    = '$relationship',
   profession    = '$profession',
   favmusic    = '$music',
   sexuality    = '$sexuality',
   members_info.info    = '$message'
   WHERE members.username = members_info.username AND members.username = '$User_Session'
   LIMIT 1";

[jaymc]

No, doesnt work. Heres what I used

 

$q = "UPDATE members m, members_info mi
SET
fullname 	= '$fullname',
loc 		= '$location',
nightclub 	= '$nightclub',
footyteam 	= '$football',
relationship 	= '$relationship',
profession 	= '$profession',
favmusic 	= '$music',
sexuality 	= '$sexuality',
mi.info 	= '$message'
WHERE m.username = mi.username AND m.username = '$User_Session'
LIMIT 1";

[mwasif]

I just realized that you can not use LIMIT in multiple table update. It can only be used when updating single table.

[aschk]

Doesn't surprise me that it won't work with UPDATE. How does it know which row to update if it matches more than 1?

[jaymc]

Glad this has been cleared up, did think it was strange!