I have an SQL delete query
There are 2 tables: report (which has a primary key of reportId and a bit field called migrated) and report_detail (which has a foreign key of reportId). I want to delete all the rows from report_detail that have a reportId whic开发者_StackOverflow社区h, in the report table, has migrated = 1. This is the select query that selects all the rows I want:
select *
from report r inner join report_detail d
on r.reportId = d.reportId
where migrated = 1
Will this delete query do what I want or am I doing something wrong?
delete from report_detail
where exists(
select *
from report r inner join report_detail d
on r.reportId = d.reportId
where migrated = 1
)
DELETE FROM report_detail
WHERE
report_detail.reportId IN
(
SELECT reportId
FROM report
WHERE migrated = 1
)
delete from report_detail d
inner join report r
on r.reportId = d.reportId
where migrated = 1
That will likely delete everything in your table.
try this instead:
delete d
from report_detail d
inner join report r
on r.reportId = d.reportId
where migrated = 1
MySQL has a way to delete from a particular table, while joining with other tables:
DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;
Or:
DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;
精彩评论