Results 1 to 4 of 4

Thread: Adding Info

  1. #1
    Join Date
    Jan 2012
    Posts
    52

    Unanswered: Adding Info

    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

  2. #2
    Join Date
    Jan 2012
    Posts
    52
    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?

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Jan 2012
    Posts
    52
    Thanks for the help Wim I really appreciate it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •