CODEWITHSUNDEEP
phpmysqljoin
Sugumar Venkatesan
Sugumar Venkatesan

Reputation: 4028

delete one table with join on multiple table with where condition

I am trying to delete a row from skills_jobs table

following is my query 

delete from skills_jobs skj inner join jobs j on skj.jobid=j.id inner join users u on u.id=j.userid where u.id=$userid

but it throws the following error;

Fatal error: Uncaught exception 'Exception' with message '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 'skj inner join jobs j on skj.jobid=j.id inner join users u on u.id=j.userid wher' at line 1 query: delete from skills_jobs skj inner join jobs j on skj.jobid=j.id inner join users u on u.id=j.userid where u.id=41

Upvotes: 0

Views: 62

Answers (5)

Shaunak Shukla
Shaunak Shukla

Reputation: 2347

 DELETE FROM skj USING `skills_jobs` AS skj
 INNER JOIN `jobs` AS j ON skj.`jobid` = j.`id`
 INNER JOIN `users` AS u ON u.`id` = j.`userid` 
 WHERE u.`id` = $userid

If you declare an alias for a table, you must use the alias when referring to the table.

Another structured answer:

DELETE skj FROM `skills_jobs` AS skj
INNER JOIN `jobs` AS j ON skj.`jobid`=j.`id` 
INNER JOIN `users` AS u ON u.`id`=j.`userid`
WHERE u.`id`=$userid

Upvotes: 1

Hitesh Makvana
Hitesh Makvana

Reputation: 44

DELETE FROM skills_jobs skj INNER JOIN jobs j ON j.id=skj.jobid INNER JOIN 
users u ON u.id=j.userid 
WHERE u.id=$userid

Upvotes: 1

maximkou
maximkou

Reputation: 5332

You must set table, from which you remove row, then try:

DELETE skills_jobs from skills_jobs INNER JOIN jobs j ON skills_jobs.jobid=j.id INNER JOIN users u ON u.id=j.userid WHERE u.id=$userid

See section "Multi-Table Deletes" from https://dev.mysql.com/doc/refman/5.7/en/delete.html

Upvotes: 2

Sagar Gangwal
Sagar Gangwal

Reputation: 7937

delete from skills_jobs skj 
inner join jobs j ON skj.jobid=j.id 
inner join users u ON u.id=j.userid 
where u.id=$userid

Try above code. Hope this will helps.

Upvotes: 1

zenwraight
zenwraight

Reputation: 2000

It should be like this

skills_jobs skj inner join jobs j on skj.jobid=j.id

You forgot on in the syntax

So the correct syntax will look like this:-

delete from skills_jobs skj inner join jobs j on skj.jobid=j.id inner join users u on u.id=j.userid where u.id=$userid

Hope this helps!

Upvotes: 1

Related Questions