Post reply

Update through join

  • November 16, 2004 at 2:56 am

    #361614

    Hi there,

    I wan't to update a table with names from another table. My problem is that I don't know how to do it, besides I don't have a singel unique key between the tables (I have to combinate two columns in order to make a join).

    So I looks like this,

    Update Table_A

    Set Table_A.Names = Table_B.Names_Correct

    From Table_A Inner Join

    Table_B On Table_A.Column_1 = Table_B.Column_1 And Table_A.Column_2 = Table_B.Column_2

    This  is not working, if anyone has a solution, hints, advice etc. please help me.

    Thanks alot

      

  • November 16, 2004 at 3:18 am

    #530245

    hi Joe,

     

    you should probably look up "normalisation" and "foreign Keys" in the books online help  (BOL). 

    Also, If I were you I wouldn’t worry about inner joins at the moment..Keep it simple, then you can build on it

     

    Here's an example. I have 2x tables:

    1) tblCompany

    companyId        (Primary key, integer datataype)

    companyName    (varChar)

     

    2)tblStaff

    staffId              (Primary key, integer datataype)

    staffName           (varChar)

    companyId           (foreignKey,integer datataype)

     

    ...tblStff.companyId links to tblCompany.companyId

     

    I have an employee with a staffId = 3, he works for the company that has companyId =10

    NOW, If I want to change the company that  this employee works for, I can run the following query:

     

    UPDATE tblStaff

    SET  companyId  = 5

    WHERE staffId=3

     

    I hope this has cleared a few things up.

    yogi

     

  • November 16, 2004 at 3:35 am

    #530247

    Hi Yogi

    Thanks for replying at this hour.

    Well, I'm working with a database which have been build with focus on storing data, not querying.

    So I don't have an ID which connects the two tables, I have make an inner join with two columns between the two tables.

    Regrads Joe

      

  • November 16, 2004 at 5:50 am

    #530255

    Hi there

    The query I posted before,

    Update Table_A

    Set Table_A.Names = Table_B.Names_Correct

    From Table_A Inner Join

    Table_B On Table_A.Column_1 = Table_B.Column_1 And Table_A.Column_2 = Table_B.Column_2

    It works! - when you have the same design of the tables (int, char, lenght etc.). My Name_Correct was Char with lenght 256 and Names was Char with length 25.

    Joe Joe

     

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

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