SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Feb 2004
    Location
    Winnipeg Canada
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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. #2
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,181
    Mentioned
    65 Post(s)
    Tagged
    2 Thread(s)
    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:

    Code:
    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.

    Code:
    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.

    Code:
    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. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by codamedia View Post
    The example below is not real...

    ANIMALS
    animalid
    farmid
    animalname
    dateadded
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,181
    Mentioned
    65 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by r937 View Post
    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?
    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.


Bookmarks

Posting Permissions

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