Post reply

Updating Table

  • February 9, 2005 at 12:14 pm

    #68048

    I am trying to update a table, based on information from another table.  The two tables can be linked together.
     
    Table 1 Structure "T1"
    -AcctNo  (target field for update)
    -CustNo
    -Office (always NULL)
     
    Table 2 Structure "T2"
    -AccountNo
    -CustNo
    -Office (sometimes NULL sometimes ' ')
     
    I need to put the AccountNo from T2 into the AcctNo field in T1 where the CustNo and Office match up.
     
    First problem is I cannot use the Office in the join because of the inconsistent value (Null and ' ').  Therefore, I have split up the update in two; one for Office and one for blank/null Office.  Example of the Null or Empty update query:
     
    --Begin Update

    Update T1
    Set AcctNo =
        (select T2.AccountNo
         from T2
         where (T2.Office is null or T2.Office = ' ')  )

    where T2.CustNo = T1.CustNo

    and T1.Office is null

    --End Update
     
    This produces the following error:
     
    Server: Msg 107, Level 16, State 2, Line 1

    The column prefix 'T2' does not match with a table name or alias name used in the query.

     
    The two tables are in separate databases, but I have simplified the example.
    Any help with this query would be greatly appreciated!
     
    J

  • February 9, 2005 at 12:22 pm

    #541446

    update T1

    set acctno = t2.accountno

    from t2 join t1 on t1.custno = t2.custno

    where (t2.office is null or t2.office='') and t1.office is null

    Gordon Pollokoff

    "Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones

  • February 9, 2005 at 12:25 pm

    #541448

    I think I could provide better help if I could see the whole query and some sample data... I'm pretty sure that my solution is incomplete but it might get you started.

    Update T1 set T1.AcctNo = T2.AcctNo from T1 inner join T2 on T1.Custno = T2.Custno and ISNULL(T2.Office, '') = '' and T1.Office IS NULL

    EDITED :

    Damn got beat to the punch

  • February 9, 2005 at 1:04 pm

    #541454

    Thanks to both of you!  We got this resolved.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply