Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Posts
    233

    Unanswered: update table from sub qry

    what am i doing wrong here:

    UPDATE dbo.TblPolicy p
    set Undr_Trans_Cnt =U.SumU,
    set Acct_Trans_Cnt =A.SumA

    INNER JOIN
    (
    SELECT AEGIS_Ref_No, Count(*) as SumU
    FROM dbo.TblUndrPrmDtl
    WHERE Trans_Status_ind ='A'
    GROUP BY AEGIS_Ref_No
    ) as U
    ON p.AEGIS_Ref_No = u.AEGIS_Ref_No
    INNER JOIN
    (
    SELECT AEGIS_Ref_No, Count(*) as SumA
    FROM dbo.TblAcctPrmDtl
    WHERE Status_IND ='A'
    GROUP BY AEGIS_Ref_No
    ) as A
    ON p.AEGIS_Ref_No = A.AEGIS_Ref_No

  2. #2
    Join Date
    Apr 2007
    Posts
    183
    What is the error you get?
    Microsoft SQL Server MVP

    N 5604'39.26"
    E 1255'05.63"

  3. #3
    Join Date
    Apr 2007
    Posts
    183
    UPDATE dbo.TblPolicy p
    set Undr_Trans_Cnt =U.SumU,
    Acct_Trans_Cnt =A.SumA
    INNER JOIN
    (
    SELECT AEGIS_Ref_No, Count(*) as SumU
    FROM dbo.TblUndrPrmDtl
    WHERE Trans_Status_ind ='A'
    GROUP BY AEGIS_Ref_No
    ) as U
    ON p.AEGIS_Ref_No = u.AEGIS_Ref_No
    INNER JOIN
    (
    SELECT AEGIS_Ref_No, Count(*) as SumA
    FROM dbo.TblAcctPrmDtl
    WHERE Status_IND ='A'
    GROUP BY AEGIS_Ref_No
    ) as A
    ON p.AEGIS_Ref_No = A.AEGIS_Ref_No
    Microsoft SQL Server MVP

    N 5604'39.26"
    E 1255'05.63"

  4. #4
    Join Date
    Apr 2007
    Posts
    183
    You probably get an error due to the two SET operarators?
    Microsoft SQL Server MVP

    N 5604'39.26"
    E 1255'05.63"

  5. #5
    Join Date
    Apr 2007
    Posts
    183
    I thought I did when I posted a solution for you
    07-25-09, 06:57
    Microsoft SQL Server MVP

    N 5604'39.26"
    E 1255'05.63"

  6. #6
    Join Date
    Oct 2003
    Posts
    233
    Quote Originally Posted by Peso
    You probably get an error due to the two SET operarators?
    yes that is why, i broke it into two updates and it works, can you do two sets in one sql statement?

  7. #7
    Join Date
    Apr 2007
    Posts
    183
    I thought I did in my reply 07-25-09, 06:57
    Microsoft SQL Server MVP

    N 5604'39.26"
    E 1255'05.63"

  8. #8
    Join Date
    Oct 2003
    Posts
    233
    it was also missing FROM & tableName

    UPDATE dbo.TblPolicy p
    set Undr_Trans_Cnt =U.SumU,
    Acct_Trans_Cnt =A.SumA
    FROM TblPolicy
    ....


    Thanks for the help

  9. #9
    Join Date
    Apr 2007
    Posts
    183
    Great!
    You adapted and learned. This is how knowledge stays.
    Microsoft SQL Server MVP

    N 5604'39.26"
    E 1255'05.63"

Posting Permissions

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