Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2012

    Unanswered: Returning only most recent result

    I am trying to update my date base using the update and set function. The issue I am having is multiple records being returned using the following code below.

    Obviously the issues lies in firstly multiple contacts being assigned to different accounts and it cannot update when two records are returned. I need to be able to pull all contacts listed to a particular contact, get the most recent and update the field. Then move on to the next account etc Is there a way to return the most recently entered record? I have done queries in the past with SELECT MAX etc but cannot seem to write something that works, maybe I am completely off track!

    Below is the basic code I was able to write one using the LIMIT function but this required looking at unique accounts one at a time then ordering by DESC creation date and LIMIT 1

    UPDATE accounts
    SET accounts.contact_name = (SELECT contacts.first_name
    FROM contacts, accounts_contacts
    WHERE accounts_contacts.account_id = 
    AND accounts_contacts.contact_id =
    Last edited by BridgeCat; 08-30-12 at 01:24.

  2. #2
    Join Date
    Sep 2009
    San Sebastian, Spain
    I think the first thing you need to look at is defining the latest contact details in terms of a query. In this case here you need something like (assuming that the creation date is in the accounts_contacts table):

    SELECT account_id, contact_name
    FROM contacts c
    INNER JOIN accounts_contacts ac USING (contact_id)
      SELECT account_id, MAX(creation_date) as creation_date 
      FROM accounts_contacts 
      GROUP BY account_id) max ON (max.creation_date = ac.creation_date AND max.account_id = ac.account_id)
    This has not been tested but gives you an idea of what to do. If this is a one off operation, you might consider creating a temporary table with the account_id and latest contact and then updating against the temporary table values. If this is something that needs to happen more often then add the code into the update statement.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    Follow me on Twitter

  3. #3
    Join Date
    Jun 2012
    Thanks worked perfectly

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts