Search code examples
mysqlsqljoinsql-delete

DELETE query with Joins

I am running a query to delete from table.

delete from sps
inner join str on str.studentid = sps.studentid 
where str.studentid like '%2012%psy%' 
  and str.semesterid=2 
inner join papers on papers.id = sps.paperid 
where papers.c_id=10 
  and papers.p_semid=2

I don't know why it is not showing any result and giving me an error.

The error is :

#1064 - 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 'inner join str on str.studentid=sps.studentid where str.studentid like '%2012%ps' at line 2

Solution

  • Move the two predicates of the two WHERE clauses into one WHERE clause at the end of your query, as described by the Mysql DELETE syntax. Something like:

    DELETE s 
FROM sps s
INNER JOIN str ON str.studentid = s.studentid 
INNER JOIN papers ON papers.id = s.paperid 
WHERE str.studentid LIKE '%2012%psy%' 
  AND str.semesterid = 2 
  AND papers.c_id = 10 
  AND papers.p_semid = 2