CODEWITHSUNDEEP
phpmysqlsql
Ronak Patel
Ronak Patel

Reputation: 390

delete from multiple table with inner join

I am trying to delete record from 3 tables in 1 sql query in php. First, I tried it with delete records from two tables. This is the query for that:

DELETE pa, pr FROM pollanswers pa INNER JOIN pollresults pr ON
pa.PollQuestionId=pr.PollQuestionId WHERE pa.PollQuestionId = '123';

The problem is, what if there is no PollQuestionId in one of these table.. And other thing after that how to integrate it with third table?

Thanks.

Upvotes: 0

Views: 100

Answers (3)

Legionar
Legionar

Reputation: 7597

You should not delete from multiple tables in one query.

You can define foreign key constraints on the tables with ON DELETE CASCADE option.

Then deleting the record from parent table removes the records from child tables.

Check this link : http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

Upvotes: 3

Ronak Patel
Ronak Patel

Reputation: 390

I have figured it out guys.. Thanks for your effort...

here is the query to join 3 tables:

DELETE po, pa, pr FROM posts po
LEFT JOIN pollanswers pa ON pa.PollQuestionId=po.PostId
LEFT JOIN pollresults pr ON pr.PollQuestionId=po.PostId
WHERE po.PostId = '123';

Upvotes: 0

quietkatalyst
quietkatalyst

Reputation: 107

To join a third table, you could add another inner join:

DELETE pa, pr FROM pollanswers pa
INNER JOIN pollresults pr ON pa.POllQuestionID=pr.PollQuestionId
INNER JOIN pollwhatevers pw ON pw.whatevercolumn=pa.PollquestionID
WHERE pa.PollQuestionId = '123';

But if you want to join it to a table that is the result of joining PollResults and PollAnswers, you may want to consider using a temporary table. See this link for more information,I'm not sure I can explain it as well as this does:

http://devzone.advantagedatabase.com/dz/webhelp/Advantage7.1/adssql/using_temporary_tables_in_sql_statements.htm

Upvotes: -1

Related Questions