CODEWITHSUNDEEP
sqlpostgresqlsql-update
Karthik sa
Karthik sa

Reputation: 49

How to update a column in a table A using the value from another table B wherein the relationship between tables A & B is 1:N by using max() function

I have two tables namely loan_details and loan_his_mapping with 1:N relationship. I need to set the hhf_request_id of loan_details table by the value which is present in the loan_his_mapping table for each loan.

Since the relationship is 1:N , I want to consider the record for each loan from loan_his_mapping table with two conditions mentioned below. The table definitions are as follows:

   CREATE TABLE public.loan_details 
 (
       loan_number   bigint NOT NULL,
        hhf_lob     integer,
        hhf_request_id   integer,
        status  character varying(100),
         CONSTRAINT loan_details_pkey PRIMARY KEY (loan_number)
     );


             CREATE TABLE public.loan_his_mapping
          (
               loan_number           bigint NOT NULL,
                spoc_id               integer NOT NULL,
               assigned_datetime     timestamp without time zone,
               loan_spoc_map_id bigint NOT NULL,
                line_of_business_id   integer,
                request_id bigint,
          CONSTRAINT loan_spoc_his_map_id PRIMARY KEY (loan_spoc_map_id),
          CONSTRAINT fk_loan_spoc_loan_number_his FOREIGN KEY (loan_number)
         REFERENCES public.loan_details (loan_number) MATCH SIMPLE
          ON UPDATE NO ACTION ON DELETE NO ACTION );

The joining conditions while updating are:

  1. The Records of loan_details with hhf_lob = 4 and status='Release'
  2. I should consider that record for updating value among 'N' number of records from loan_his_mapping table with value max(loan_spoc_map_id) for each loan.

The query I have right now 

 update lsa_loan_details ldet
 set hhf_request_id = history.request_id 
 from loan_his_mapping history  
 where ldet.loan_number = history.loan_number and ldet.status='Release' and  ldet.hhf_lob=4 and 
 history.line_of_business_id=4 ;

I want to know how to use that record for each loan from loan_his_mapping with max(loan_spoc_map_id) to update column of loan_details table. Please Assist!

Upvotes: 0

Views: 126

Answers (1)

user330315
user330315

Reputation:

You need a sub-query to fetch the row corresponding to the highest loan_spoc_map_id

Something along the lines:

update loan_details ldet
   set hhf_request_id = history.request_id 
from (
  select distinct on (loan_spoc_map_id) loan_number, request_id
  from loan_his_mapping  lhm
  where lhm.line_of_business_id = 4
  order by loan_spoc_map_id desc
) as history  
where ldet.loan_number = history.loan_number 
  and ldet.status = 'Release' 
  and ldet.hhf_lob = 4;

Upvotes: 1

Related Questions