Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

Using an Multi-value update stagement with an agregate function

I am trying to determine the best way to rewrite the following query.

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 = 'Y'
FROM      dbo.EOT_SupTbl_01 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


I would like to only update the min value of dbo.EOT_SupTbl_01.Date1 but at the same time updating the other values. For example:

UPDATE dbo.EOT_SupTbl_02 SET dbo.EOT_SupTbl_02.Date1 = (Select MIN(dbo.EOT_SupTbl_01.Date1),
&#9;   dbo.EOT_SupTbl_02.Date2 = dbo.EOT_SupTbl_01.Date2,
&#9;   dbo.EOT_SupTbl_02.Orig = dbo.EOT_SupTbl_01.Orig,
&#9;   dbo.EOT_SupTbl_02.Pct = dbo.EOT_SupTbl_01.Pct,
&#9;   dbo.EOT_SupTbl_02.Result = 'Y'
FROM      dbo.EOT_SupTbl_01 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)
0
sapgonzalez
Asked:
sapgonzalez
  • 9
  • 6
1 Solution
 
ajisasaggiCommented:
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

Open in new window

0
 
ajisasaggiCommented:
If you want to set the minimum value of the column on all updated rows, then just remove the WHERE clause from the subquery.
0
 
ajisasaggiCommented:
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

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sapgonzalezAuthor Commented:
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.
0
 
sapgonzalezAuthor Commented:
Looks like there a couple alias assigned to dbo.EOT_SupTbl_01.
0
 
sapgonzalezAuthor Commented:
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
0
 
ajisasaggiCommented:
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.
0
 
sapgonzalezAuthor Commented:
Thanks for the advice. Are you saying the query I posted was what you were trying to accomplish.

0
 
sapgonzalezAuthor Commented:
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.
0
 
ajisasaggiCommented:
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.
0
 
sapgonzalezAuthor Commented:
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'
0
 
sapgonzalezAuthor Commented:
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)
0
 
sapgonzalezAuthor Commented:
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.
0
 
ajisasaggiCommented:
Try this.

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(asd.Date1) FROM dbo.EOT_SupTbl_02 asd)
0
 
sapgonzalezAuthor Commented:
Thanks ajisasaggi...that got me past the SQL error. On to the actually problem I am trying to solve.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now