jaymc Posted March 25, 2008 Share Posted March 25, 2008 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 Link to comment https://forums.phpfreaks.com/topic/97818-solved-delete-join/ Share on other sites More sharing options...
aschk Posted March 25, 2008 Share Posted March 25, 2008 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. Link to comment https://forums.phpfreaks.com/topic/97818-solved-delete-join/#findComment-500467 Share on other sites More sharing options...
fenway Posted March 25, 2008 Share Posted March 25, 2008 That depends on the relationship between the tables... but yes, if 1-to-1, then limit 1, not limit 5. Link to comment https://forums.phpfreaks.com/topic/97818-solved-delete-join/#findComment-500683 Share on other sites More sharing options...
jaymc Posted March 29, 2008 Author Share Posted March 29, 2008 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"; Link to comment https://forums.phpfreaks.com/topic/97818-solved-delete-join/#findComment-504082 Share on other sites More sharing options...
mwasif Posted March 30, 2008 Share Posted March 30, 2008 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"; Link to comment https://forums.phpfreaks.com/topic/97818-solved-delete-join/#findComment-504789 Share on other sites More sharing options...
jaymc Posted March 30, 2008 Author Share Posted March 30, 2008 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"; Link to comment https://forums.phpfreaks.com/topic/97818-solved-delete-join/#findComment-505020 Share on other sites More sharing options...
mwasif Posted March 31, 2008 Share Posted March 31, 2008 I just realized that you can not use LIMIT in multiple table update. It can only be used when updating single table. Link to comment https://forums.phpfreaks.com/topic/97818-solved-delete-join/#findComment-505768 Share on other sites More sharing options...
aschk Posted April 1, 2008 Share Posted April 1, 2008 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? Link to comment https://forums.phpfreaks.com/topic/97818-solved-delete-join/#findComment-506280 Share on other sites More sharing options...
jaymc Posted April 10, 2008 Author Share Posted April 10, 2008 Glad this has been cleared up, did think it was strange! Link to comment https://forums.phpfreaks.com/topic/97818-solved-delete-join/#findComment-513759 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.