sql - 如何通过使用存储过程在另一个表中查找列的值存在来更新一个表中的批量记录？

Question

我有两个表，即'loan_details'和'loan_mapping'。我需要通过loan_details 表中存在的值来设置loan_mapping 表的mod_id。因此，仅当 'loan_mapping' 表的loan_number 列存在于 'loan_detail' 表记录中时，才会批量更新 'loan_mapping' 表的记录（20K 记录）。（我的意思是如果使用loan_number查找成功）

两个表的定义如下：

CREATE TABLE public.loan_details 
(
  loan_number   bigint NOT NULL,
  crp_lob       integer,
  mod_id        integer,
  CONSTRAINT loan_details_pkey PRIMARY KEY (loan_number)
);

CREATE TABLE public.loan_mapping 
(
  loan_number           bigint NOT NULL,
  spoc_id               integer NOT NULL,
  assigned_datetime     timestamp without time zone,
  loan_spoc_map_id      integer NOT NULL DEFAULT nextval ('lsa_loan_spoc_mapping_loan_spoc_map_id_seq'::regclass),
  line_of_business_id   integer,
  mod_id                integer,
  CONSTRAINT loan_spoc_mapping_pk PRIMARY KEY (loan_spoc_map_id),
  CONSTRAINT fk_loan_spoc_loan_number FOREIGN KEY (loan_number) 
    REFERENCES public.loan_details (loan_number)
);

因此，我需要确切的存储过程来执行我总结的问题中提到的所需更新。

标签： sqlpostgresqlstored-procedurespostgresql-9.6

Answer 1

我在这里看不到存储过程的任何原因：

update loan_mapping lm
  set mod_id = ld.mod_id
from loan_details ld  
where ld.loan_number = lm.loan_number;