Delete query with inner join

Question

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

Pரதீப் · Accepted Answer · 2016-01-16 15:57:18Z

4

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

edited Jan 16, 2016 at 15:57

answered Jan 16, 2016 at 15:51

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vamsi Prabhala · Accepted Answer · 2016-01-16 15:51:09Z

4

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

answered Jan 16, 2016 at 15:51

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

Collectives™ on Stack Overflow

Delete query with inner join

query

error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

query

error

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related