Update table problem

[DrewConn]

I have two tables where I need to update from one into the other . The table I am updating contains account numbers which is the primary key.

The table I am getting the data from also has an account number that ties to the first table. The field I need however can contain two values for every Account number.

So in short I need to flatten out this data into my main table.

I have tried this in an update statement as follows:

update B
set CIS_NUM1 = MIN(C.APP_ID), CIS_NUM2 = MAX(C.APPL_ID)
FROM BUSCARDS_WKLY B JOIN CIS_LINK C
ON B.ACCT_NUM = C.APPL_ID

However this gives me the following:
An aggregate may not appear in the set list of an UPDATE statement.

How can I update the first occurance of the appl_id into the CIS_Num1 field and the second occurance into the CIS_Num2 field.

Thanks.

 

[zhavic]

I am not sure, but did you mean this ?

UPDATE B
	SET CIS_NUM1 = App.Min_APP_ID, CIS_NUM2 = App.Max_APP_ID
FROM BUSCARDS_WKLY B
	INNER JOIN 	( SELECT MIN(APP_ID) AS Min_APP_ID, MAX(APP_ID) AS Max_APP_ID, APPL_ID
					FROM CIS_LINK
					GROUP BY APPL_ID  -- get values for each row from BUSCARDS_WKLY
				 ) AS App ON B.ACCT_NUM = App.APPL_ID

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[rac2]

JOIN to a subquery instead of to the table. The subquery can provide the values you need for each account number.

UPDATE B SET
   CIS_NUM1 = LittleID
   , CIS_NUM2 = BigID
FROM BUSCARDS_WKLY B
JOIN ( SELECT ACCT_NUM
       , MIN(C.APP_ID) AS LittleID
       , MAX(C.APPL_ID) AS BigID
       FROM CIS_LINK
       GROUP BY ACCT_NUM
     ) C
ON B.ACCT_NUM = C.ACCT_NUM

I am confused however when you JOIN the tables on

B.ACCT_NUM = C.APPL_ID

but you say you have two values of C.APPL_ID for each value of B.ACCT_NUM. {If a == b and b != c Then a != c}

My query assumes that the table CIS_LINK actually has some column which can be used to JOIN it to BUSCARDS_WKLY; I used ACCT_NUM for this.

 

[DrewConn]

Nope, I am looking for the minimum(or first) value the and the max(or second) value tied to the acct_num field.

 

[rac2]

Minimum with respect to what?

Perhaps an example of the data showing a few rows from each table would help.