Question 331736 in Mysql Question posted in
The official MySQL documentation can be found here.

mysql update multiple columns based on most current match in another table

Running mysql: 5.7.44

I have a table wInvoices where I need to update two columns based on the values of the most recent matching record from another table, wPayments. The wPayments table holds all CC transactions, success and declined, for each invoice using wInvoices invID as the binding/matching column. Each invID could have multiple wPayments transactions but I only need to get the most current one; and use the values from that match to populate the wInvoices columns.

invID,  paidDate,  paidDate, (other columns) = wInvoices
===============================
1001    
1002
1003
1004

pid,  invID,  mDate (other columns)  = wPayments
===================================
2000  1001    2020-07-21 09:31:00 **
2001  1001    2020-07-20 08:20:20
2002  1001    2020-07-20 11:30:30
2003  1002    2020-07-20 09:09:00 **
2004  1003    2020-07-20 11:21:00
2005  1002    2020-07-19 13:30:00 
2006  1003    2020-07-20 13:58:00 **
2007  1004    2020-07-20 09:45:00
2008  1004    2020-07-20 10:00:00 **

So that the new values of wInvoices is:

invID,  paidDate,             paidID, (other columns)
===========================================================
1001    2020-07-21 09:31:00   2000
1002    2020-07-20 09:09:00   2003
1003    2020-07-20 13:58:00   2006
1004    2020-07-20 10:00:00   2008

I have read multiple threads on similar types of updates, but have not been able to find anything that matches my exact requirement. The challenge is accounting for the most recent wPayments record. The closet I can come with is this:

UPDATE wInvoices inv SET
  inv.iPaidDate=pay.pDate
  inv.iPaidReceipt=pay.pid
FROM (select pid,pDate from wPayments) pay
 WHERE where inv.invID=pay.invID ;

…but this does not account for the most recent wPayments record and could end up populating wInvoice columns with data from older records.

Tags:

2

Answers


  1. 0

    Easiest way to do this IMO is:

    UPDATE wInvoices inv
INNER JOIN wPayments pay ON inv.invID=pay.invID
SET
    inv.iPaidDate=pay.pDate
    inv.iPaidReceipt=pay.pid
WHERE NOT EXISTS (
    SELECT 1
    FROM wPayments newer
    WHERE newer.invID=pay.invID AND
        (newer.mDate,newer.pid) > (pay.mDate,pay.pid)
)

    But I question your database model; why not just select the newest payment information along with the invoice rather than storing it?

    Login or Signup to reply.
  2. 0

    Here’s how I would write the UPDATE:

    UPDATE wInvoices inv
INNER JOIN wPayments pay ON inv.invID=pay.invID
LEFT OUTER JOIN wPayments pay2 ON inv.invID=pay2.invID AND pay2.pid > pay.pid
SET
    inv.iPaidDate=pay.pDate
    inv.iPaidReceipt=pay.pid
WHERE pay2.pid IS NULL;

    The join to pay2 is an alternative to the WHERE NOT EXISTS solution. If we know that pay correlates to any row for the given invID, we don’t want any row pay2 to exist such that it has the same invID and a greater pid. We want pay to be only the row with the greatest pid.

    So use an outer join, together with a WHERE clause to find only the cases where no row pay2 is found with a greater pid. That means pay must be the row with the greatest pid of the set of rows matching the respective invID.

    I’m assuming pid is unique.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search