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
SET accounts.contact_name = (SELECT contacts.first_name
FROM contacts, accounts_contacts
WHERE accounts_contacts.account_id = accounts.id
AND accounts_contacts.contact_id = contacts.id)
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)
INNER JOIN (
SELECT account_id, MAX(creation_date) as creation_date
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.