CODEWITHSUNDEEP
sqlsql-server-2000sql-delete
dmr
dmr

Reputation: 22403

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 which, 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
)

Upvotes: 2

Views: 136

Answers (4)

Russ
Russ

Reputation: 4173

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

Upvotes: 2

Simmo
Simmo

Reputation: 3131

DELETE FROM report_detail
WHERE 
    report_detail.reportId IN
    (
        SELECT reportId 
        FROM report 
        WHERE migrated = 1
    )

Upvotes: 3

Beth
Beth

Reputation: 9617

delete from report_detail d 
inner join report r 
on r.reportId = d.reportId 
where migrated = 1

Upvotes: 2

Sjoerd
Sjoerd

Reputation: 75659

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;

Upvotes: 1

Related Questions