SQL - How to answer this question: remove all employees with the letter M in the 4th position-CodePudding

What I need to do:

write a command that will remove all employees from the employees table with the letter M in the 4th position

DELETE FROM employees e
  JOIN jobs j 
    ON JOIN e.job_id = j.job_id 
 WHERE j.job_title LIKE '__M%';

Ended with error: "SQL command not properly ended"

But why? I'm working on Oracle sample database, something like this: https://livesql.oracle.com/apex/livesql/s/k8zkivxrwp3xddekpqldtecc8

Please let me know what I am doing wrong.

Thank you so much

CodePudding user response：

You can't use a DELETE statement in a manner in which a SELECT statement is written(apart from the misusage of the SELECT statement due to repeatedly used JOIN keyword), but use the following as alternative

DELETE ( SELECT * 
           FROM employees e 
           JOIN jobs j 
             ON e.job_id = j.job_id
          WHERE SUBSTR(UPPER(j.job_title),4,1)='M' )

CodePudding user response：

Your join is badly written

DELETE FROM employees e
JOIN jobs j ON e.jon_id = j.job_id 
WHERE j.job_title LIKE '__M%';

CodePudding user response：

You can use:

DELETE FROM employees e
WHERE EXISTS (
        SELECT 1
        FROM   jobs j 
        WHERE  e.job_id = j.job_id 
        AND    j.job_title LIKE '___M%'
      );