1

Trying to change this SELECT statement

SELECT t1.user_id FROM jforum_users t1 WHERE EXISTS 
      (SELECT t3.user_id 
          FROM jforum_users t3 
             LEFT JOIN jforum_posts t2 ON t3.user_id=t2.user_id  
          WHERE user_website IS NOT NULL 
             AND t2.user_id IS  null 
             AND t1.user_id=t3.user_id
       );

into a DELETE statement

DELETE FROM jforum_users t1 WHERE EXISTS
   (SELECT t3.user_id 
       FROM jforum_users t3 
          LEFT JOIN jforum_posts t2 ON t3.user_id=t2.user_id  
       WHERE user_website IS NOT NULL
         AND t2.user_id IS NULL 
         AND t1.user_id=t3.user_id
   );

The SELECT statement works but DELETE but doesnt like it giving error:

ERROR 1064 (42000): 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 exists (select t3.user_id from jforum_users t3 left join jforum_posts t2 o' at line 1

what am i doing wrong ?

Loopo
  • 2,204
  • 2
  • 28
  • 45
Paul Taylor
  • 13,411
  • 42
  • 184
  • 351
  • Try:`DELETE t1 FROM jforum_users AS t1 WHERE EXISTS (select t3.user_id from jforum_users t3 left join jforum_posts t2 on t3.user_id=t2.user_id where user_website is not null and t2.user_id is null and t1.user_id=t3.user_id);` – Lukasz Szozda Sep 23 '15 at 17:43
  • Is the only difference 'AS' - this made no difference – Paul Taylor Sep 23 '15 at 17:51
  • Not only `DELETE t1 FROM jforum_users AS t1` – Lukasz Szozda Sep 23 '15 at 17:51
  • This should work for you: DELETE FROM jforum_users where user_id in(SELECT t1.user_id from jforum_users t1 where exists ( select t3.user_id from jforum_users t3 left join jforum_posts t2 on t3.user_id=t2.user_id where user_website is not null and t2.user_id is null and t1.user_id=t3.user_id)); – sagi Sep 23 '15 at 18:02
  • @eggyal Very rude close the question that way. Sometime even when you read the manual you can miss the answer. Even you give a wrong answer. – Juan Carlos Oropeza Sep 23 '15 at 20:09
  • @JuanCarlosOropeza: The question is fundamentally a dupe; closing it is not "rude", it's how this site is supposed to work. And my answer is not wrong. Learn to read before you criticise. – eggyal Sep 23 '15 at 21:49
  • 1
    @eggyal Your close is rude because you are saying go read the manual. Under that line of thinking all the question here should have same reply. And that particular error is very broad, even a missing coma can cause that error and reading the manual doesnt help that. Sometimes another set of eyes can help to solve an issue. – Juan Carlos Oropeza Sep 23 '15 at 21:58
  • 1
    @JuanCarlosOropeza: Wow. I post the (correct) answer to the question, and redirect the OP to a long post I spent considerable time writing in order to explain what Error #1064 means and how people can decipher it in order to solve any problem of this sort in the future. It's open to the OP to modify his question and/or post a new one to explain, in light of that advice, what steps they have taken should they still be having problems—the question may then be reopened if it's not a dupe after all. That's not rude, it's how this site works. If you think otherwise, take it up on [Meta]. – eggyal Sep 23 '15 at 22:00
  • 2
    @eggyal I have to say I didnt find your post at all helpful the so called duplicate answer just explains the blindingly obvious that the sql syntax was incorrect but not how to fix it. My question was how to transform a particular type of SELECT statement into an equivalent DELETE, its clearly not the same question. (In the end I took the approach of using a creating table from a select, and then using that to help with my delete with an IN clause. ) – Paul Taylor Sep 24 '15 at 19:32
  • 1
    @PaulTaylor I was wondering how to solve your problem and create a new [**Question**](http://stackoverflow.com/questions/32770114/delete-using-multiple-tables-and-repeat-table-in-the-subquery) for you. This use `JOIN` instead of `EXISTS`. Let me know if that sample help you. For that example I create a simple sqlFiddle. For your problem not sure about the schema so I try to create a base [SqlFiddle](http://sqlfiddle.com/#!9/6bebf6) maybe you can update it closer to your case – Juan Carlos Oropeza Sep 24 '15 at 20:59
  • 1
    @PaulTaylor As eggyal say first error is the alias part, but the big problem is using the table you want to update inside the subquery. And that is explain in my other question. – Juan Carlos Oropeza Sep 24 '15 at 21:00
  • 1
    @JuanCarlos thanks for your efforts – Paul Taylor Sep 26 '15 at 06:51

0 Answers0