Update TABLE from a Related Table with Multiple Entries (only wanting the latest val)

Databases
1

The example below is not real, but if I can solve this, I know I can solve the bigger problem I have!

Using MYSQL, I have two tables. Farm and Animals. Animals can have several items connected to farm with the “farmid” serving as a related key.

FARM
id
farmname
latestanimal

ANIMALS
animalid
farmid
animalname
dateadded

What I need to do is run an update query, that automatically populates “farm.latestestanimal” with the “animalname” from the latest entry of Animals that is connected to the farm.

I know this sample below is wrong, but it might show what I am trying to do:
“UPDATE farm SET farm.latestanimal = animals.animalname WHERE animal.farmid = farm.id ORDER BY animal.dateadded DESC LIMIT 1”

Can someone point me in the right direction to making this work?

2

Well, you COULD do this however… You are then relying on this update query kicking off in order for your main table to be accurate. The proper way (in most cases) is to remove the column latest animals, and deliver it on demand in a query.

FARM
farm_id
farm_name

ANIMALS
animal_id - auto increment
farm_id
animal_name
date_added

You first want to select the latest animal for each farm_id:


SELECT
MAX(ANIMAL_ID) AS RECENT_ANIMAL_ID, FARM_ID
FROM
ANIMALS
GROUP BY 
FARM_ID

Testing this code will produce the latest entered animal, per farm id. Auto increment will handle this for you. Now we will turn this into a sub query and attach it to our master query to bring in info from the farm table.


SELECT
FARMS.FARM_ID, RECENT_ANIMAL_ID
FROM
FARMS
LEFT JOIN (
   SELECT
   MAX(ANIMAL_ID) AS RECENT_ANIMAL_ID, FARM_ID
   FROM
   ANIMALS
   GROUP BY 
   FARM_ID
) RA ON FARMS.FARM_ID = RA.FARM_ID

Now most likely, there will be some more detail on the animal farm you want to bring in, lets say animal_color exists on the animal table.


SELECT
FARMS.FARM_ID, RECENT_ANIMAL_ID, ANIMALS.ANIMAL_NAME, ANIMALS.ANIMAL_COLOR
FROM
FARMS
LEFT JOIN (
   SELECT
   MAX(ANIMAL_ID) AS RECENT_ANIMAL_ID, FARM_ID
   FROM
   ANIMALS
   GROUP BY 
   FARM_ID
) RA ON FARMS.FARM_ID = RA.FARM_ID
LEFT JOIN ANIMALS ON RA.RECENT_ANIMAL_ID = ANIMALS.ANIMAL_ID

The same theory can be applied to an update, if need be.

3

the problem with “not real” data is that the solutions we give you often share that same characteristic

do you really repeat the animal name for every farm the animal is in?

or does the same animal have different names in different farms?

do you see the nonsense that your “not real” data leads to?

4

While I agree, I also see why one might want to change over to a fake situation in order to hide their project, or their employer.