0

creditNote table

id      invoice_id     generated_at(timestamp)     status
10101      111          2018-02-28 14:42:39.247         generated
23982      111          2018-03-30 11:11:11.247          generated 

paymentModeAmount table

id      invoice_id     paid_on(timestamp)     payment_mode
98236      111                                 creditNote
63725      111                                 creditNote

After execution of below query OUTPUT is

paymentModeAmount table

id        invoice_id     paid_on(timestamp)       payment_mode
98236        111         2018-02-28 14:42:39.247   creditNote 
63725        111         2018-02-28 14:42:39.247   creditNote 

Here PaidOn Column has same data, but we need to get two different timestamps

Expected

id      invoice_id     paid_on(timestamp)       payment_mode
98236      111         2018-02-28 14:42:39.247   creditNote 
63725      111         2018-03-30 11:11:11.247   creditNote 

paidOn timestamp's should be different as expected

PL/SQL Function

CREATE OR REPLACE FUNCTION updatePaidOn()
RETURNS TABLE(
invoiceid BIGINT,
generatedat TIMESTAMP)
AS $$
BEGIN
RETURN QUERY SELECT
generated_invoice_id as invoiceid, generated_at as generatedat
FROM
credit_note
WHERE
status='generated';
END; $$

LANGUAGE 'plpgsql';

Execute the below function

UPDATE payment_mode_amount pm
SET paid_on = sq.generatedat
from updatePaidOn() as sq
where pm.invoice_id = sq.invoiceid
and pm.paid_on is null
and pm.payment_mode='creditNote';

Update accordingly timestamps..

  • 1
    You must enumerate records (totally or in some group) in both tables (as I understand the id values you show are fake and are not equal in practice) and use additional joining by this number (and group if exists). In current state both updatePaidOn() records match all conditions for all records in payment_mode_amount, so first joined record is used for update only, which cause similar result values. – Akina Dec 5 '18 at 6:11
  • Yes, id values are just fake, but when record is matched, picking only first value of generated_at timestamp and updating in credit_note with two records with same value only – Gagan Mesala Dec 5 '18 at 6:16
  • I do not see any reason to set the timestamps in the order you show. Will the desired output be correct if we will switch timestamp field values in these 2 records? – Akina Dec 5 '18 at 6:23
  • 1
    10:34:11:11:11:12 is not a timestamp. What is that supposed to be? – a_horse_with_no_name Dec 5 '18 at 6:31
  • Timestamp is a just value (will store in generated_at column), Actually we are picking generated_at values from credit note table(invoice_id='111') which matches in payment mode amount table (invoice_id = '111'), then output should be reflected in payment mode amount table(paid_on column) (with two different values pulled from credit note table). – Gagan Mesala Dec 5 '18 at 6:32
1

If you don't care which timestamp is used for which target row, you can match the two tables by joining them on a generated "row number" using e.g. this:

with cn as (
  select id, invoice_id, generated_at, row_number() over (order by id) as rn
  from creditnote
  where status = 'generated'
), pnr as (
  select id, invoice_id, generated_at, row_number() over (order by id) as rn
  from paymentmodeamount
  where payment_mode = 'creditNote'
)
select *
from pnr 
  join cn on cn.rn = pnr.rn and cn.invoice_id = pnr.invoice_id;

This joins the rows based on the ordering of the ID values in the tables creditnote and paymentmodeamount. If you want you can also order the creditnote table by generated_at.

Given your sample data this would return something like this (this is only to demonstrate what the above join does):

id    | invoice_id | generated_at | rn | id    | invoice_id | generated_at        | rn
------+------------+--------------+----+-------+------------+---------------------+---
63725 |        111 |              |  1 | 10101 |        111 | 2018-02-28 14:42:39 |  1
98236 |        111 |              |  2 | 23982 |        111 | 2018-03-30 11:11:11 |  2

Now the above query can be used as part of an UPDATE statement to bring the rows together.

with cn as (
  select id, invoice_id, generated_at, row_number() over (order by id) as rn
  from creditnote
  where status = 'generated'
), pnr as (
  select id, invoice_id, generated_at, row_number() over (order by id) as rn
  from paymentmodeamount
  where payment_mode = 'creditNote'
)
update paymentmodeamount p
   set generated_at = cn.generated_at
from pnr 
  join cn on cn.rn = pnr.rn and cn.invoice_id = pnr.invoice_id
where p.id = pnr.id
  and p.generated_at is null;

This assumes that paymentmodeamount.id is the primary key of the table.

Note that this won't be very fast for large tables.

Online example: https://rextester.com/YXAK25669

  • ERROR: syntax error at or near "update" LINE 11: update paymentmodeamount p ^ – Gagan Mesala Dec 5 '18 at 10:32
  • with cn as ( select id, generated_invoice_id, generated_at, row_number() over (order by id) as rn from credit_note where status = 'generated' ), pnr as ( select id, invoice_id, paid_on, row_number() over (order by id) as rn from payment_mode_amount where payment_mode = 'creditNote' ) update payment_mode_amount p set paid_on = cn.generated_at from pnr join cn on cn.rn = pnr.rn and cn.generated_invoice_id = pnr.invoice_id where p.id = pnr.id and p.paid_on is null; – Gagan Mesala Dec 5 '18 at 10:33
  • Thanks, but is there any alternate solution, i have more than 3 lakhs records on paymentmodeamount table. – Gagan Mesala Dec 5 '18 at 10:34
  • @GaganMesala: you probably made some error when copying and pasting the code. I just checked with 9.5 and it works there as well: i.imgur.com/6ClAGDh.png – a_horse_with_no_name Dec 5 '18 at 10:35
  • @GaganMesala: with such a database design, I don't see any other solution. – a_horse_with_no_name Dec 5 '18 at 10:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.