Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Using an Multi-value update stagement with an agregate function

Posted on 2010-08-24
17
Medium Priority
?
259 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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
 

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 2000 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

721 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