Solved

Using an Multi-value update stagement with an agregate function

Posted on 2010-08-24
17
247 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

862 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

27 Experts available now in Live!

Get 1:1 Help Now