Write a SQL delete based on a select statement

Question

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'

Put the select query inside your delete query, just like this DELETE FROM ..... WHERE kpi_results_fk = (SELECT krps.kpi_results_fk FROM .....). Your select query must return only (1) result. Just incase it returns more than (1), put ANY like this DELETE FROM ..... WHERE kpi_results_fk = ANY(SELECT krps.kpi_results_fk FROM .....) — rhavendc
– rhavendc, Commented Mar 22, 2016 at 10:11

mmuzahid · Accepted Answer · 2016-03-22 11:01:33Z

1

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')

edited Mar 22, 2016 at 11:01

answered Mar 22, 2016 at 10:47

mmuzahid

2,28025 silver badges45 bronze badges

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

Comments

Jens · Accepted Answer · 2016-03-22 10:31:15Z

1

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)

edited Mar 22, 2016 at 10:31

answered Mar 22, 2016 at 10:08

Jens

69.6k15 gold badges107 silver badges133 bronze badges

1 Comment

sagi Over a year ago

Since when in is a clause?

nnunes10 · Accepted Answer · 2016-03-22 10:08:16Z

0

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)

answered Mar 22, 2016 at 10:08

nnunes10

5504 silver badges15 bronze badges

Collectives™ on Stack Overflow

Write a SQL delete based on a select statement

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related