locked
Update one table from another RRS feed

  • Question

  • update #dates_final_TEMP
    set #dates_final_TEMP.DIVISION = INFORMATION.dbo.Specialty.DIVISION
    from #dates_final_TEMP t2
    inner
    join INFORMATION.dbo.Specialty t1
    on t2.ConsultantSpecialty = t1.SPECNAME

    I can't get this to compile correctly, i get the following error message

    "The multi-part identifier "INFORMATION.dbo.Specialty.DIVISION" could not be bound"

    Friday, June 19, 2009 11:17 AM

Answers

  • May be because you must have defined 'Division' as NOT NULL, and INFORMATION.dbo.Specialty.DIVISION
     column might be having some NULL in it.
    Add WHERE condition below it
    WHERE INFORMATION.dbo.Specialty.DIVISION IS NOT NULL

    Mangal Pardeshi BI
    SQL With Mangal
    • Marked as answer by akhlaq768 Friday, June 19, 2009 11:41 AM
    Friday, June 19, 2009 11:34 AM
  • If you have declared the alias names to tables, use them

    update t2
    set t2.DIVISION = t1.DIVISION
    from #dates_final_TEMP t2
    inner
    join INFORMATION.dbo.Specialty t1
    on t2.ConsultantSpecialty = t1.SPECNAME

    Mangal Pardeshi BI
    SQL With Mangal
    • Marked as answer by akhlaq768 Friday, June 19, 2009 11:41 AM
    Friday, June 19, 2009 11:27 AM

All replies

  • If you have declared the alias names to tables, use them

    update t2
    set t2.DIVISION = t1.DIVISION
    from #dates_final_TEMP t2
    inner
    join INFORMATION.dbo.Specialty t1
    on t2.ConsultantSpecialty = t1.SPECNAME

    Mangal Pardeshi BI
    SQL With Mangal
    • Marked as answer by akhlaq768 Friday, June 19, 2009 11:41 AM
    Friday, June 19, 2009 11:27 AM
  • tried that but get the following error

    Cannot insert the value NULL into column 'Division', table 'tempdb.dbo.#dates_final_TEMP___________________________________________________________________________________________________000000003222'; column does not allow nulls. UPDATE fails.

    The statement has been terminated.

    Friday, June 19, 2009 11:31 AM
  • May be because you must have defined 'Division' as NOT NULL, and INFORMATION.dbo.Specialty.DIVISION
     column might be having some NULL in it.
    Add WHERE condition below it
    WHERE INFORMATION.dbo.Specialty.DIVISION IS NOT NULL

    Mangal Pardeshi BI
    SQL With Mangal
    • Marked as answer by akhlaq768 Friday, June 19, 2009 11:41 AM
    Friday, June 19, 2009 11:34 AM
  • im running an update query on the table...

    UPDATE

    #dates_final_TEMP
    SET Division = 'External to Trust'
    WHERE ConsultantSpecialty = 'External Consultant'

    i now have the follwoing error message

    String or binary data would be truncated.

    The statement has been terminated.

    Friday, June 19, 2009 11:52 AM
  • What is the lenght of the Division column? Less than 19 I guess.

    Mangal Pardeshi BI
    SQL With Mangal
    Friday, June 19, 2009 12:04 PM
  • is there a quick qay of finding out?

    the table is a temp table!
    Friday, June 19, 2009 12:08 PM
  • If its temp, then you must have delcared it in the batch itself.


    Try this one
    USE tempdb
    select * from INFORMATION_SCHEMA.COLUMNS

    remember SQL Server will add something like ___________________________________________________________________________________________________000000003222
    in table name


    Mangal Pardeshi BI
    SQL With Mangal
    Friday, June 19, 2009 12:11 PM
  • character

    max length is 1

    what can i do

    Friday, June 19, 2009 1:29 PM
  • i've created a temp table using a

    SELECT
    INTO
    FROM

    i had to create 2 blank (new) column and i did this by the following statement

     , '' AS Division
     , '' AS SLA

    is this right

    Friday, June 19, 2009 1:37 PM
  • I must tell you are following very very wrong practise... with creating Table with INTO.
    And also creating one of those columns by ''.  Becasue of '' the lenght of the column is assigned to 1.

    To overcome this..you can try
    ,CONVERT(VARCHAR(50),'') AS Division.
    This way column will be created with lenght as 50.  You can specify whatever is required.

    But after giving work around, my suggestion is, DON'T use SELECT INTO to create table. 
    As you can use, by using a short cut method of creating Table you have wasted nearly 3-4 hours on on finding the solution.  A 2 mintues of writing CREATE TABLE could have saved this precious time.
    Mangal Pardeshi BI
    SQL With Mangal
    Friday, June 19, 2009 2:42 PM
  • Hi Mangal Pardeshi

    you solution for creating a column is person, this is what i need. thanks

    in regards to creating a temp table, (SELECT INTO) the only reason i did this was to temporary create a table so i can query it. my end table will always be a permanent table...

    thanks for the advice, very much appreciate... thanks
    Friday, June 19, 2009 3:10 PM
  • http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/bba9742e-897a-4038-b8ea-22f09edd0adc
    Varinder Sandhu http://varindersandhus.blogspot.com/
    • Proposed as answer by Varinder Sandhu Saturday, September 18, 2010 10:53 AM
    Saturday, September 18, 2010 10:52 AM