Solved

Using an Multi-value update stagement with an agregate function

Posted on 2010-08-24
17
244 Views
Last Modified: 2012-05-10
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
Comment
Question by:sapgonzalez
  • 9
  • 6
17 Comments
 
LVL 2

Expert Comment

by:ajisasaggi
ID: 33513815
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
 
LVL 2

Expert Comment

by:ajisasaggi
ID: 33513839
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
 
LVL 2

Expert Comment

by:ajisasaggi
ID: 33513846
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
 

Author Comment

by:sapgonzalez
ID: 33514156
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
 

Author Comment

by:sapgonzalez
ID: 33514185
Looks like there a couple alias assigned to dbo.EOT_SupTbl_01.
0
 

Author Comment

by:sapgonzalez
ID: 33514255
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
 
LVL 2

Expert Comment

by:ajisasaggi
ID: 33518350
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:sapgonzalez
ID: 33520262
Thanks for the advice. Are you saying the query I posted was what you were trying to accomplish.

0
 

Author Comment

by:sapgonzalez
ID: 33522504
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
 
LVL 2

Expert Comment

by:ajisasaggi
ID: 33523701
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
 

Author Comment

by:sapgonzalez
ID: 33524375
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
 

Author Comment

by:sapgonzalez
ID: 33534855
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
 

Author Comment

by:sapgonzalez
ID: 33541887
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
 
LVL 2

Accepted Solution

by:
ajisasaggi earned 500 total points
ID: 33542826
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
 

Author Comment

by:sapgonzalez
ID: 33545748
Thanks ajisasaggi...that got me past the SQL error. On to the actually problem I am trying to solve.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now