CODEWITHSUNDEEP
mysqlsql
clarkk
clarkk

Reputation: 27679

Delete query with inner join

query

DELETE `contact`
FROM `contact` ct
INNER JOIN `contact_user` cu ON cu.contact_id=ct.id
WHERE ct.id=4 && ct.block_id=5671 && cu.user_id=1

error

Fatal: SQLSTATE[42S02]: Base table or view not found: 1109 Unknown table 'contact' in MULTI DELETE; SQL: DELETE contact FROM contact ct INNER JOIN contact_user cu ON cu.contact_id=ct.id WHERE ct.id=4 && ct.block_id=5671 && cu.user_id=1

Upvotes: 0

Views: 149

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93694

Since you have defined Alias name to your table contact you need to use alias name instead of original table name. Try this

DELETE ct 
FROM   contact ct 
       INNER JOIN contact_user cu 
               ON cu.contact_id = ct.id 
WHERE  ct.id = 4 
       AND ct.block_id = 5671 
       AND cu.user_id = 1

or remove the alias name for contact table

DELETE contact 
FROM   contact 
       INNER JOIN contact_user cu 
               ON cu.contact_id = contact.id 
WHERE  contact.id = 4 
       AND contact.block_id = 5671 
       AND cu.user_id = 1

Upvotes: 4

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Because you have defined an alias for the table, you should use it. Also replace && with and.

DELETE `ct` --`contact`
FROM `contact` ct
INNER JOIN `contact_user` cu ON cu.contact_id=ct.id
WHERE ct.id=4 and ct.block_id=5671 and cu.user_id=1

Upvotes: 4

Related Questions