Adding Info

buzmay · #1 (**permalink**) 01-24-12, 08:18

Hi guys,

So this is my question. I have a table where I am adding data, based on two other tables. The problem is that when I make a normal join I get an error saying that the subquery returned more than 1 value. This is because in the list I have there are some companies that are repeated several times so the code that returns this error is this one

Code:

UPDATE CC_SPLIT SET CC_ACCOUNT_AMOUNT =
(SELECT AMOUNT FROM EXP_DATA
WHERE PERIOD = '1111'
AND EXP_DATA.ACCOUNT = CC_SPLIT.CC_ACCOUNT
AND EXP_DATA.ACCOUNT = CC_SPLIT.CC_COMPANY)

So I came with this as a solution but the problem is that the following only insert data in those companies that are repeated and have different ammounts

Code:

UPDATE CC_SPLIT SET CC_ACCOUNT_AMOUNT =
(SELECT SUM (AMOUNT) FROM EXP_DATA
WHERE PERIOD = '1111'
AND EXP_DATA.ACCOUNT = CC_SPLIT.CC_ACCOUNT
AND EXP_DATA.ACCOUNT = CC_SPLIT.CC_COMPANY)

So my question is how can I add into CC_ACCOUNT_AMOUNT the other data left?

Any ideas?
Thanks in advance

buzmay · #2 (**permalink**) 01-24-12, 09:05

Is something like this possible?

Code:

IF CC_ACCOUNT_AMOUNT_NOV11 IS NOT NULL 

BEGIN

UPDATE CC_SPLIT SET CC_ACCOUNT_AMOUNT =
(SELECT AMOUNT FROM EXP_DATA
WHERE PERIOD = '1111'
AND EXP_DATA.ACCOUNT = CC_SPLIT.CC_ACCOUNT
AND EXP_DATA.ACCOUNT = CC_SPLIT.CC_COMPANY)

END

??? Any ideas how to fix this?

Wim · #3 (**permalink**) 01-24-12, 09:29

I think there is a bug in your code
AND EXP_DATA.ACCOUNT = CC_SPLIT.CC_COMPANY)

Try this:

Code:

UPDATE U 
SET U.CC_ACCOUNT_AMOUNT = T.AMOUNT
FROM CC_SPLIT AS U
	INNER JOIN (SELECT SUM(AMOUNT) as Amount, 
			ACCOUNT, CC_COMPANY
		FROM EXP_DATA 
		WHERE PERIOD = '1111'
		GROUP BY ACCOUNT, CC_COMPANY
		) AS T ON
		T.ACCOUNT = U.CC_ACCOUNT
		AND T.CC_COMPANY = U.CC_COMPANY

buzmay · #4 (**permalink**) 01-24-12, 10:35

Thanks for the help Wim

I really appreciate it.

Internet Services

Web Development

Digital Marketing

Consumer Tech