locked
update value of current table to other table RRS feed

  • Question

  • <br/>
    update  Prisoner
    set Picture=dbo.Personal.photo_num 
    where FinanceNo=dbo.Personal.FinanceNo
    

    i write  above error and he  give me this error 

    Msg 4104, Level 16, State 1, Line 3
    The multi-part identifier "dbo.Personal.FinanceNo" could not be bound.

    Saturday, January 1, 2011 8:11 PM

Answers

  • I think you probably meant:

     

    update Prisoner set Picture = P.Photo_Num
    
    from Prisoner inner join dbo.Personal P on Prisoner = P.FinanceNo
    

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by LekssEditor Monday, January 3, 2011 10:20 PM
    • Marked as answer by WeiLin Qiao Tuesday, January 11, 2011 1:51 AM
    Sunday, January 2, 2011 1:07 AM

All replies

  • Hi KhaLeeL ZoURoB,

    It's hard to correct a statement without knowing what exactly you want to achieve. But my first guess would be

    UPDATE Prisoner
    SET    Picture = p.photo_num
    FROM   dbo.Personal AS p
    WHERE  FinanceNo = p.FinanceNo;

    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    • Proposed as answer by WeiLin Qiao Monday, January 3, 2011 8:58 AM
    • Unproposed as answer by WeiLin Qiao Tuesday, January 11, 2011 1:51 AM
    Saturday, January 1, 2011 9:46 PM
  • I think you probably meant:

     

    update Prisoner set Picture = P.Photo_Num
    
    from Prisoner inner join dbo.Personal P on Prisoner = P.FinanceNo
    

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by LekssEditor Monday, January 3, 2011 10:20 PM
    • Marked as answer by WeiLin Qiao Tuesday, January 11, 2011 1:51 AM
    Sunday, January 2, 2011 1:07 AM