Link to home
Start Free TrialLog in
Avatar of Morpheus7
Morpheus7

asked on

Update table using MIN() Function

Hi,
I am trying to update a date field using the MIN() function. when I run the code below, I just get one date  result, instead of a result for each appropriate row in the temp table.  Any one any ideas on what I am doing wrong here. All date fields are SMALLDATETIME.

UPDATE i
SET datefst = (SELECT MIN(ar.sales_date)
                        FROM prodSales ar
                        JOIN #TCI i
                        ON i.ProdCode = ar.Prodcode
                        AND i.catl = ar.catl
                        WHERE ar.sales_date BETWEEN i.fdate AND i.End_Date)
FROM #TCI i
JOIN prodSales ar
ON i.ProdCode = ar.ProdCode
AND i.catl = ar.catl
AND i.datefst IS NULL;


Many thanks
ASKER CERTIFIED SOLUTION
Avatar of bcnagel
bcnagel
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Vitor Montalvão
Can you explain the logic of what you trying to do?
Avatar of Morpheus7
Morpheus7

ASKER

That works Great, many thanks