How do I update a column with a value based on the latest row of a child table in mySQL?

I have two tables: Item and ItemHistory with a one to many relationship.

I want to flatten the 'item' table a bit and add: dtLastUpdated and nLastUpdatedByUserID columns but I need to upgrade the table data.

Item

nID

dtCreated

dtLastUpdated (new column)

nLastUpdatedByUserID (new column)

ItemHistory

nID

nItemID

dtUpdated

nUpdatedByUserID

Can someone help me to create an update statement to do 开发者_运维技巧this. I have tried various things which arent legal in mySQL so am a bit stuck. For example...

UPDATE Item
INNER JOIN ItemHistory ON ItemHistory.nItemID=Item.nID
SET Item.dtLastUpdated = ItemHistory.dtUpdated,
    Item.nLastUpdatedByUserID = ItemHistory.nUpdatedByUserID
ORDER BY ItemHistory.dtUpdated DESC

This is required for a mySQL 5.5 installation.

Thanks

update Item as I
inner join (select IH.nItemID,
                   IH.dtUpdated,
                   IH.nUpdatedByUserID
            from ItemHistory as IH
            inner join (select nItemID, 
                               max(dtUpdated) as dtUpdated
                        from ItemHistory
                        group by nItemID) as IHLast
            on IH.nItemID = IHLast.nItemID and
               IH.dtUpdated = IHLast.dtUpdated) as IH
on IH.nItemID = I.nID
set
  I.dtLastUpdated = IH.dtUpdated,
  I.nLastUpdatedByUserID = IH.nUpdatedByUserID;

The innermost query gets nItemID and max(dtUpdated) to be joined back to ItemHistory to get nUpdatedByUserID for where max(dtUpdated) = dtUpdated. There might be other/better ways of doing this in MySQL but this seams to work as expected.

Dan, your statement will never work because you are updating a column in the item table that doesn't even exist.

According to you, the Item table only has nID, dtCreated and yet you are doing an update on the "dtLastUpdated" column in the Item table which doesn't exist. Similary with the nLastUpdatedByUserID.

See your statement here:

UPDATE Item INNER JOIN ItemHistory ON ItemHistory.nItemID=Item.nID 
SET Item.dtLastUpdated=ItemHistory.dtUpdated,   
Item.nLastUpdatedByUserID=ItemHistory.nUpdatedByUserID 
ORDER BY ItemHistory.dtUpdated DESC

You need to create those columns first in the Item table: Here's the reference manual for doing that in MySQL. Regards.

EDIT: After reading your comment, I am correcting your sql statment (this seems to be the syntax on MySQL for doing updates with inner joins):

UPDATE from Item as I inner join ItemHistory as IH ON IH.nItemID=I.nID
SET I.dtLastUpdated=IH.dtUpdated,   
I.nLastUpdatedByUserID=IH.nUpdatedByUserID