Using an Multi-value update stagement with an agregate function

Is this what you are looking for?

Replace <grouping_key> with the column by which you want to perform the aggregate operation.

UPDATE dbo.EOT_SupTbl_02
SET dbo.EOT_SupTbl_02.Date1 = (select min(b.Date1) from dbo.EOT_SupTbl_01 b where a.<grouping_key> = b.<grouping_key>), 
         dbo.EOT_SupTbl_02.Date2 = dbo.EOT_SupTbl_01.Date2, 
         dbo.EOT_SupTbl_02.Orig = dbo.EOT_SupTbl_01.Orig, 
         dbo.EOT_SupTbl_02.Pct = dbo.EOT_SupTbl_01.Pct, 
         dbo.EOT_SupTbl_02.Result = 'Y'
FROM      dbo.EOT_SupTbl_01 a INNER JOIN dbo.EOT_SupTbl_02 ON dbo.EOT_SupTbl_01.MatNum = dbo.EOT_SupTbl_02.MatNum AND 
          dbo.EOT_SupTbl_01.Date1 <= dbo.EOT_SupTbl_02.BegDate AND dbo.EOT_SupTbl_01.Date2 >= dbo.EOT_SupTbl_02.BegDate

Select allOpen in new window

If you want to set the minimum value of the column on all updated rows, then just remove the WHERE clause from the subquery.

In other words...

UPDATE dbo.EOT_SupTbl_02
SET dbo.EOT_SupTbl_02.Date1 = (select min(b.Date1) from dbo.EOT_SupTbl_01 b), 
         dbo.EOT_SupTbl_02.Date2 = dbo.EOT_SupTbl_01.Date2, 
         dbo.EOT_SupTbl_02.Orig = dbo.EOT_SupTbl_01.Orig, 
         dbo.EOT_SupTbl_02.Pct = dbo.EOT_SupTbl_01.Pct, 
         dbo.EOT_SupTbl_02.Result = 'Y'
FROM      dbo.EOT_SupTbl_01 a INNER JOIN dbo.EOT_SupTbl_02 ON dbo.EOT_SupTbl_01.MatNum = dbo.EOT_SupTbl_02.MatNum AND 
          dbo.EOT_SupTbl_01.Date1 <= dbo.EOT_SupTbl_02.BegDate AND dbo.EOT_SupTbl_01.Date2 >= dbo.EOT_SupTbl_02.BegDate

Select allOpen in new window

getting the follwing errors

Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'dbo.EOT_SupTbl_01' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'dbo.EOT_SupTbl_01' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'dbo.EOT_SupTbl_01' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'dbo.EOT_SupTbl_01' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'dbo.EOT_SupTbl_01' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'dbo.EOT_SupTbl_01' does not match with a table name or alias name used in the query.

Looks like there a couple alias assigned to dbo.EOT_SupTbl_01.

Thanks. I think maybe it should be like:

UPDATE dbo.EOT_SupTbl_02 SET dbo.EOT_SupTbl_02.Date1 = (select min(b.Date1) from dbo.EOT_SupTbl_01 b),
         dbo.EOT_SupTbl_02.Date2 = a.Date2,
         dbo.EOT_SupTbl_02.Orig = a.Orig,
         dbo.EOT_SupTbl_02.Pct = a.Pct,
         dbo.EOT_SupTbl_02.Result = 'Y'
FROM      dbo.EOT_SupTbl_01 a INNER JOIN dbo.EOT_SupTbl_02 ON a.MatNum = dbo.EOT_SupTbl_02.MatNum AND
         a.Date1 <= dbo.EOT_SupTbl_02.BegDate AND a.Date2 >= dbo.EOT_SupTbl_02.BegDate

Got confused by the table names.
But the idea is to use a subquery to retrieve the minimum date.

Also, it is a good practice to use aliases for tables in a  query. This improves readability.

Thanks for the advice. Are you saying the query I posted was what you were trying to accomplish.

Hey ajisasaggi, maybe you can answer this one for me. I am trying to find a MIN date for a particular Orig. I was under the impression that I could use MIN on dates also. The following query

select Orig, Pct,min(dbo.EOT_SupTbl_01.Date1) from dbo.EOT_SupTbl_01
where MatNum = '010512-0003'
Group By Orig, Pct

Returns

1082      50.0      2009-04-01 00:00:00.000
1051      50.0      2009-04-01 00:00:00.000
1082      100.0      2010-06-01 00:00:00.000

I should only get record number 1 and 2 but not 3.

3 records were returned because the grouping is being done on both Orig and Pct. So MIN is calculated for each unique pair of Orig and Pct.

Thats actually what I thought after looking at it again, so I change the query to the following and still got the three records.

Select *
From (Select Orig, Min(Convert(DATETIME,dbo.EOT_SupTbl_01.Date1)) as MTKDATE1 from dbo.EOT_SupTbl_01 Group BY Orig) as a
INNER JOIN dbo.EOT_SupTbl_01 ON a.Orig = dbo.EOT_SupTbl_01.Orig
Where MatNum = '010512-0003'

I am trying to rewite the original query because it does not seem like it was pulling the results correctly. that query is
_________________________________________________________________________________________
UPDATE dbo.EOT_SupTbl_02 SET dbo.EOT_SupTbl_02.Date1 = (select min(b.Date1) from dbo.EOT_SupTbl_01 b),
         dbo.EOT_SupTbl_02.Date2 = a.Date2,
         dbo.EOT_SupTbl_02.Orig = a.Orig,
         dbo.EOT_SupTbl_02.Pct = a.Pct,
         dbo.EOT_SupTbl_02.Result = 'N'
FROM      dbo.EOT_SupTbl_01 a INNER JOIN dbo.EOT_SupTbl_02 ON a.MatNum = dbo.EOT_SupTbl_02.MatNum AND
          a.Date1 > dbo.EOT_SupTbl_02.BegDate
___________________________________________________________________________________________

I have tried the following two ways and neither works. Any help would be greatly appreciated.


UPDATE dbo.EOT_SupTbl_02 AS a2, dbo.EOT_SupTbl_01 AS b1
SET a2.Date1 = b1.Date1,
         a2.Date2 = b1.Date2,
         a2.Orig = b1.Orig,
         a2.Pct = b1.Pct,
         a2.Result = 'N'
WHERE (MIN(b1.date1 > a2.BegDate)) b1.MatNum = a2.MatNum
___________________________________________________________________________________________
UPDATE dbo.EOT_SupTbl_02 SET dbo.EOT_SupTbl_02.Date1 = dbo.[EOT_SupTbl_01].Date1,
         dbo.EOT_SupTbl_02.Date2 = dbo.[EOT_SupTbl_01].Date2,
         dbo.EOT_SupTbl_02.Orig = dbo.[EOT_SupTbl_01].Orig,
         dbo.EOT_SupTbl_02.Pct = dbo.[EOT_SupTbl_01].Pct,
         dbo.EOT_SupTbl_02.Result = 'N'
WHERE EXISTS
( Select dbo.EOT_SupTbl_01.Date1 FROM dbo.EOT_SupTbl_01 WHERE
      MIN(dbo.EOT_SupTbl_01.Date1 )> dbo.EOT_SupTbl_02.BegDate AND
      dbo.EOT_SupTbl_01.MatNum = dbo.EOT_SupTbl_02.MatNum)

I have simplified the query to

UPDATE b
SET b.Date1 = a.Date1,
         b.Date2 = a.Date2,
         b.Orig = a.Orig,
         b.Pct = a.Pct,
         b.Result = 'N'
FROM      dbo.EOT_SupTbl_01 a, dbo.EOT_SupTbl_02 b
WHERE       a.MatNum = b.MatNum AND
       b.BegDate < (Select MIN(b.Date1) FROM dbo.EOT_SupTbl_02)

but still cant figure out why I get the following error

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Tried changing the where clause to HAVING but no luck.

Thanks ajisasaggi...that got me past the SQL error. On to the actually problem I am trying to solve.