CODEWITHSUNDEEP
mysqlsql
Night Walker
Night Walker

Reputation: 21260

Write a SQL delete based on a select statement

How I can make the following query and delete in one query ?

select krps.kpi_results_fk from report.kpi_results_per_scene krps inner join report.kpi_results kr on kr.session_uid = '0000c2af-1fc8-4729-bb2a-d4516a63107a' 
and kr.pk = krps.kpi_results_fk

delete from report.kpi_results_per_scene where kpi_results_fk = 'answer from above query'

Upvotes: 2

Views: 75

Answers (3)

mmuzahid
mmuzahid

Reputation: 2270

I think for your case, NO need to use inner join.

Following query could reduce the overhead of inner join

DELETE FROM report.kpi_results_per_scene 
WHERE kpi_results_fk IN 
        (SELECT kr.pk FROM report.kpi_results kr 
            WHERE kr.session_uid = '0000c2af-1fc8-4729-bb2a-d4516a63107a')

Upvotes: 1

Jens
Jens

Reputation: 69440

use IN operator:

delete from report.kpi_results_per_scene where kpi_results_fk in (
select krps.kpi_results_fk from report.kpi_results_per_scene krps inner join report.kpi_results kr on kr.session_uid = '0000c2af-1fc8-4729-bb2a-d4516a63107a' 
and kr.pk = krps.kpi_results_fk)

Upvotes: 1

nnunes10
nnunes10

Reputation: 550

Try the code bellow:

delete from report.kpi_results_per_scene
where kpi_results_fk IN (select krps.kpi_results_fk
                        from report.kpi_results_per_scene krps inner join report.kpi_results kr
                        on kr.session_uid = '0000c2af-1fc8-4729-bb2a-d4516a63107a' 
                        and kr.pk = krps.kpi_results_fk)

Upvotes: 0

Related Questions