Adam Posted March 16, 2009 Share Posted March 16, 2009 Hi guys. MySQL is certainly not my strongest point! I have a problem in the syntax of this query: delete from auth_user_temp aut where aut.user_id in ( select user_id from user_group where (userclass_id = 25 or userclass_id = 6) group by user_id having count(userclass_id) = 2 ); Basically the sub query returns records that have the userclass_id of 25 or 2, nothing else, and no less. The select sub query works fine by itself. I think the problem is using "aut.user_id in ..." Does any body have an alternative or, perhaps be able to tell me why it's not working? Thanks! Adam Link to comment https://forums.phpfreaks.com/topic/149623-solved-sub-query-syntax-error/ Share on other sites More sharing options...
Mchl Posted March 16, 2009 Share Posted March 16, 2009 Why do you have 'aut' in the first line? Uhh...an alias of course What is the error returned by MySQL? Link to comment https://forums.phpfreaks.com/topic/149623-solved-sub-query-syntax-error/#findComment-785715 Share on other sites More sharing options...
Adam Posted March 16, 2009 Author Share Posted March 16, 2009 "You have an error in your syntax near line 2" .. and shows virtually all the query! V. helpful! Adam Link to comment https://forums.phpfreaks.com/topic/149623-solved-sub-query-syntax-error/#findComment-785728 Share on other sites More sharing options...
Adam Posted March 16, 2009 Author Share Posted March 16, 2009 Oops: Basically the sub query returns records that have the userclass_id of 25 or 6 ** Link to comment https://forums.phpfreaks.com/topic/149623-solved-sub-query-syntax-error/#findComment-785729 Share on other sites More sharing options...
fenway Posted March 16, 2009 Share Posted March 16, 2009 Oops: Basically the sub query returns records that have the userclass_id of 25 or 6 ** So? Link to comment https://forums.phpfreaks.com/topic/149623-solved-sub-query-syntax-error/#findComment-785733 Share on other sites More sharing options...
Adam Posted March 17, 2009 Author Share Posted March 17, 2009 To avoid any "did you mean to put where (userclass_id = 25 or userclass_id = 2)" kind of responses... Anyways, can anybody help me with the query? Thanks, Adam Link to comment https://forums.phpfreaks.com/topic/149623-solved-sub-query-syntax-error/#findComment-786591 Share on other sites More sharing options...
fenway Posted March 17, 2009 Share Posted March 17, 2009 What's the error now? Link to comment https://forums.phpfreaks.com/topic/149623-solved-sub-query-syntax-error/#findComment-786668 Share on other sites More sharing options...
Adam Posted March 17, 2009 Author Share Posted March 17, 2009 It's the same? delete from auth_user_temp aut where aut.user_id in ( select user_id from user_group where (userclass_id = 25 or userclass_id = 6) group by user_id having count(userclass_id) = 2 ); Returns: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where aut.user_id in ( select user_id from user_group where (userclass_i' at line 2 Cheers, Adam Link to comment https://forums.phpfreaks.com/topic/149623-solved-sub-query-syntax-error/#findComment-786685 Share on other sites More sharing options...
fenway Posted March 17, 2009 Share Posted March 17, 2009 Bizarre... and if you actually specify a new IDs directly it's fine? Link to comment https://forums.phpfreaks.com/topic/149623-solved-sub-query-syntax-error/#findComment-786993 Share on other sites More sharing options...
Adam Posted March 18, 2009 Author Share Posted March 18, 2009 Hah! Noo.. I was just making myself clearer in the explanation of the problem before - got our wires crossed I think! Link to comment https://forums.phpfreaks.com/topic/149623-solved-sub-query-syntax-error/#findComment-787534 Share on other sites More sharing options...
fenway Posted March 19, 2009 Share Posted March 19, 2009 which mysql version? Link to comment https://forums.phpfreaks.com/topic/149623-solved-sub-query-syntax-error/#findComment-788270 Share on other sites More sharing options...
Adam Posted March 19, 2009 Author Share Posted March 19, 2009 .. 4.1 currently but we're planning to upgrade soon. I'd still like to know if anyone has any suggestions as to why that wouldn't work, but I have made a work around in PHP to solve my problem now. Thanks, Adam Link to comment https://forums.phpfreaks.com/topic/149623-solved-sub-query-syntax-error/#findComment-788318 Share on other sites More sharing options...
fenway Posted March 27, 2009 Share Posted March 27, 2009 Can you post the table structures? Link to comment https://forums.phpfreaks.com/topic/149623-solved-sub-query-syntax-error/#findComment-795230 Share on other sites More sharing options...
Adam Posted March 27, 2009 Author Share Posted March 27, 2009 Hah, it's okay now. Problem has been fixed using PHP.. Database is pretty damn complex so not worth trying to wrap your head around it! Cheers anyway though! Link to comment https://forums.phpfreaks.com/topic/149623-solved-sub-query-syntax-error/#findComment-795269 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.