Post reply

update

  • November 21, 2008 at 8:57 pm

    #92832

    can we update table A column using table B column .

    Please can anyone explain with example

  • November 21, 2008 at 10:07 pm

    #901966

    This is actually simple, and I'm pretty sure you could find an example in Books Online. Why don't you check it out.

  • November 21, 2008 at 10:20 pm

    #901968

    is that neccessary to have joining column from both the tables

  • November 21, 2008 at 10:29 pm

    #901969

    I don't know. You haven't shown me what you are talking about.

    You might also want to read the first article linked in my signature block below. It will give you excellent advice on how best to ask questions that will get you better answers.

  • November 24, 2008 at 3:46 pm

    #902795

    Heres an example of what you can do. Note that you DO need a common ID to join the two table...

    Firstly some example tables. We have two tables with names - but the first table is missing the last name. We will use #names2 to populate the lname into @names.

    CREATE TABLE #names(

    [num] [int] IDENTITY(1,1) NOT NULL,

    [fname] [varchar](25) NULL,

    [lname] [varchar](25) NULL

    )

    insert #names(fname,lname)

    select 'Bart' , 'x'

    union all select 'Bob' , 'x'

    union all select 'Carl' , 'x'

    union all select 'Hommer' , 'x'

    union all select 'Lenny' , 'x'

    union all select 'Tom' , 'x'

    CREATE TABLE #names2(

    [num] [int] IDENTITY(1,1) NOT NULL,

    [fname] [varchar](25) NULL,

    [lname] [varchar](25) NULL

    )

    insert #names2(fname,lname)

    select 'Bart' , 'Simpson'

    union all select 'Bob' , 'Jones'

    union all select 'Carl' , 'Bell'

    union all select 'Hommer' , 'Simpson'

    union all select 'Lenny' , 'Thomson'

    union all select 'Tom' , 'Smith'

    It is usually best to try to write a select query that shows the original values and the new values. This allows a proper verification of the data.

    select #names.*, #names2.lname

    from #names inner join #names2 on #names.num = #names2.num

    Finally, change the select to an update query. Keep the FROM clause the same

    UPDATE #names set #names.lname = #names2.lname

    from #names inner join #names2 on #names.num = #names2.num

    I hope that example helps.

    B

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

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