Post reply

Table update using most recent amendment

  • May 18, 2010 at 8:14 am

    #221593

    Hi,

    I need to update a table based around the following T-SQL query:

    UPDATE dbo.LSR_Prepare

    SET

    Sample_Date = dupes.Sample_Date,

    Priority = dupes.Priority,

    Customer_Code = dupes.Customer_Code,

    Product_Code = dupes.Product_Code,

    Product_Description = dupes.Product_Description,

    Format_Code = dupes.Format_Code,

    Supplier_Code = dupes.Supplier_Code,

    Supplier_Name = dupes.Supplier_Name,

    Batch_No = dupes.Batch_No,

    GRN_No = dupes.GRN_No,

    Load_Quantity = dupes.Load_Quantity,

    Reference = dupes.Reference,

    [Status] = dupes.[Status],

    Company = dupes.Company,

    Data_Source_Id = dupes.Data_Source_Id,

    Date_Loaded = dupes.Date_Loaded

    FROM dbo.LSR_Prepare fullTable LEFT JOIN dbo.LSR_Prepare_temp_AX_Update dupes

    WHERE dupes.AB_Id = fullTable.AB_Id

    AND dupes.A_Code = fullTable.A_Code

    AND fullTable.Status = 'New'

    AND dupes.Status = 'Amended'

    The problem I have is that there may be several rows in dbo.LSR_Prepare_temp_AX_Update

    which match the same row in dbo.LSR_Prepare (i.e. a one-to-many relationship). Based on the same AB_Id & A_Code.

    I only need to apply the most recent 'Amended' Status to dbo.LSR_Prepare. I have 2 other fields available in dbo.LSR_Prepare_temp_AX_Update where I can establish the most recent matching AB_Id & A_Code.

    Establishing the most recent amendment

    'Id' (int) could be used as it is sequential (use largest number)

    or

    'Sample_Date' (smalldatetime) could also be used to find the last date/time.

    Any ideas please?

    Thanks in advance,

  • May 18, 2010 at 8:36 am

    #1168862

    Typically to find the latest, I've used a correlated subquery.

    select a.SalesDate as 'MostRecentDate'

    from Sales a

    where a.SalesDate = (select max( SalesDate)

    from Sales B

    where a.SalesID = b.SalesID)

    You can add a self join to the temp and correlate it by IDs, getting the max()

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply