Using MIN/MAX in update

Question

Post reply

Using MIN/MAX in update

FreeHansje

SSChampion

Points: 11751
More actions
February 2, 2006 at 3:40 am

#152723

I have 2 tables where 1 or more persons share the same customerID:
CREATE Table Table1(
CustID INT)
CREATE TABLE Table2(
CNr INT,
CName VARCHAR(50),
C_DOB DATETIME )
So you can have CustID = 1 with 3 different persons and thus 3 different DOBs.
I need to INSERT the MINimim DOB and MAXimum DOB of persons in a third table:
CREATE TABLE Table3 (
CustID INT,
CNr INt,
DOB_MIN DATETIME,
DOB_MAX DATETIME )
At first I thought to insert MIN(DOB) into the Table1.DOB_MIN, then to update DOB_MAX with MAX(DOB) of the same CustD. However, I tun into the message, that an aggregate may not appear in the set list of an UPDATE statement.
I am breaking my head on how to solve this. Anyone can give me a hint?
Greetz,
Hans Brouwer

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

You can do it in one step(if you had custID in table 2 that is)

Insert into Table3 (CustID, DOB_Min, DOB_Max)

SELECT CustID, MIN(C_DOB), MAX(C_DOB) FROM Table2 GROUP BY CustID

How do table1 and table2 relate? What goes into CustID and what into CNr in table 3?

Could you post some sample data please?

To answer the question of how you do aggregates n an update, you use a subquery

UPDATE tbl SET MaxValue = Derived.MaxValue

FROM (SELECT Max(Something) AS MaxValue GROUP BY ID from OtherTable) Derived

WHERE tbl.ID=Derived.ID

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

FreeHansje SSChampion Points: 11751 More actions · Answer 2

You are right, I forgot something in Table1:

CREATE Table Table1(

CustID INT,

CNr INT)

There are more datafields, but for what I need they're not necessary to get the data I need.

There is a 3rd table, which is linked to Table1:

CREATE TABLE ParentTable (

loan_nr INT,

CustID INT)

Again, there are more fields, but these are not necessary for what I need.

Greetz,
Hans Brouwer

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Ah, then you should be able to get what you want in Table3 with a single insert, no need for a seperate update after.

INSERT INTO Table3(custID, DOB_Min, DOB_Max)

SELECT Table1.CustID, MIN(Table2.DOB), MAX(Table2.DOB)

FROM Table1 INNER JOIN Table2 ON Table1.CNr = Table2.CNr

GROUP BY Table1.CustID

Give that a try and let me know if it works.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

FreeHansje SSChampion Points: 11751 More actions · Answer 4

Gila, tnx for answering. This seems to work. I am a bit amazed, for I thought I had tried this and found it not working. Now it does work...

I must have been mistaken.

Tnx again.

Greetz,
Hans Brouwer