none
Update fail because Subquery returned more than 1 value error...

    질문

  • Hi,

    I need to update System.Facil.EffectiveDate column if there is 0 CommitAmount correspond to that account... (One account has multipleamount)

    Update fail because Subquery returned more than 1 value 'error'.......Can you help me how can I achieve that update ?

    Is it possible to update (if result set match) below condiltion? 

    BEGIN TRAN

    DROP TABLE #AcctNum

    CREATE TABLE #AcctNum
    (AcctId int NOT NULL ,
    AcctNum nvarchar (15))

    INSERT INTO #AcctNum ( AcctId, AcctNum )
    SELECT AcctId, AcctNum FROM SystemData.DimAccts;

    UPDATE System.Facil
    SET System.Facil.EffectiveDate = (SELECT d.EffectiveDate FROM System.DimAccts a
    JOIN System.Accts b ON b.AcctId = a.AcctId
    JOIN System.DimFaci c ON b.AcctId = c.PrimaryAcctId
    JOIN System.Facil d ON d.FacilityId = c.FacilityId
    JOIN #AcctNum an ON an.AcctId = a.AcctId
    WHERE d.CommitmentAmount <> 0 AND a.AcctNum = an.AcctNum)

    FROM 
    System.DimAcct a
    JOIN System.Acct b ON b.AcctId = a.AcctId
    JOIN System.DimFacil c ON b.AcctId = c.PrimaryAcctId
    JOIN System.Facil d ON d.FacilId = c.FacilId 
    JOIN #AcctNum an2 ON an2.AcctId = a.AcctId
    WHERE a.AcctNum = an2.AcctNum
    AND  d.CommitAmount = 0

    2018년 6월 14일 목요일 오전 7:01

모든 응답

  • Hi Dee201,

    You mean to want this?

    BEGIN TRAN
    
    DROP TABLE #AcctNum
    
    CREATE TABLE #AcctNum
     (AcctId int NOT NULL ,
     AcctNum nvarchar (15))
    
    INSERT INTO #AcctNum ( AcctId, AcctNum )
     SELECT AcctId, AcctNum FROM SystemData.DimAccts;
    
    UPDATE System.Facil
     SET System.Facil.EffectiveDate = (SELECT MAX(d.EffectiveDate) FROM System.DimAccts a 
     JOIN System.Accts b ON b.AcctId = a.AcctId
     JOIN System.DimFaci c ON b.AcctId = c.PrimaryAcctId
     JOIN System.Facil d ON d.FacilityId = c.FacilityId 
     JOIN #AcctNum an ON an.AcctId = a.AcctId
     WHERE d.CommitmentAmount <> 0 AND a.AcctNum = an.AcctNum)
    
    FROM  
     System.DimAcct a 
     JOIN System.Acct b ON b.AcctId = a.AcctId
     JOIN System.DimFacil c ON b.AcctId = c.PrimaryAcctId
     JOIN System.Facil d ON d.FacilId = c.FacilId  
     JOIN #AcctNum an2 ON an2.AcctId = a.AcctId
     WHERE a.AcctNum = an2.AcctNum
     AND  d.CommitAmount = 0
    
     END TRAN

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018년 6월 14일 목요일 오전 7:16
  • i think all you need is this

    no need of temporary table etc

    BEGIN TRAN
    
    
    UPDATE t
    SET EffectiveDate = CommitDate
    FROM
    (
    SELECT d.EffectiveDate,
    MAX(CASE WHEN d.CommitmentAmount <> 0 THEN d.EffectiveDate END) OVER (PARTITION BY a.AcctNum) AS CommitDate
    FROM  
    System.DimAccts a 
    JOIN System.Accts b ON b.AcctId = a.AcctId
    JOIN System.DimFacil c ON b.AcctId = c.PrimaryAcctId
    JOIN System.Facil d ON d.FacilId = c.FacilId  
    )t
    WHERE d.CommitAmount = 0


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 6월 14일 목요일 오전 7:26
  • BEGIN TRAN

    DROP TABLE #AcctNum

    CREATE TABLE #AcctNum
    (AcctId int NOT NULL ,
    AcctNum nvarchar (15))

    INSERT INTO #AcctNum ( AcctId, AcctNum )
    SELECT AcctId, AcctNum FROM SystemData.DimAccts;

    UPDATE System.Facil
    SET System.Facil.EffectiveDate = (SELECT d1.EffectiveDate FROM System.DimAccts a1
    JOIN System.Accts b1 ON b1.AcctId = a1.AcctId
    JOIN System.DimFaci c1 ON b1.AcctId = c1.PrimaryAcctId
    JOIN System.Facil d1 ON d1.FacilityId = c1.FacilityId 
    JOIN #AcctNum an ON an.AcctId = a1.AcctId
    WHERE d1.CommitmentAmount <> 0 AND a1.AcctNum = an.AcctNum AND a1.AcctNum = an2.AcctNum)

    FROM  
    System.DimAcct a 
    JOIN System.Acct b ON b.AcctId = a.AcctId
    JOIN System.DimFacil c ON b.AcctId = c.PrimaryAcctId
    JOIN System.Facil d ON d.FacilId = c.FacilId  
    JOIN #AcctNum an2 ON an2.AcctId = a.AcctId
    WHERE a.AcctNum = an2.AcctNum
    AND  d.CommitAmount = 0

    Try this.


    mohammad waheed

    2018년 6월 14일 목요일 오후 2:33