Delete records from Postgresql

问题: I need to delete records from one table based on inner join condition on other two tables, however query runs for ages. DELETE FROM public.blacklist WHERE subject_id NOT...

问题:

I need to delete records from one table based on inner join condition on other two tables, however query runs for ages.

DELETE FROM public.blacklist WHERE subject_id NOT IN(
SELECT DISTINCT a.id FROM public.subject a
INNER JOIN stg.blacklist_init b ON a.subject_id=b.customer_code);

Any ideas how to achieve this?

Thank you.


回答1:

You can use NOT EXISTS instead of NOT IN, and I think you don't need a DISTINCT

DELETE FROM public.blacklist bl
 WHERE NOT EXISTS  (
                     SELECT 0 
                       FROM public.subject a
                      INNER JOIN stg.blacklist_init b 
                         ON a.subject_id=b.customer_code
                      WHERE a.id = bl.subject_id 
                     );
  • 发表于 2019-03-22 01:47
  • 阅读 ( 163 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除