locked
Update one table from another RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    1
    Sign in to vote
    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    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

  • Question
    Sign in to vote
    0
    Sign in to vote
    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
  • Question
    Sign in to vote
    1
    Sign in to vote
    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    is there a quick qay of finding out?

    the table is a temp table!
    Friday, June 19, 2009 12:08 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    character

    max length is 1

    what can i do

    Friday, June 19, 2009 1:29 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
  • Question
    Sign in to vote
    1
    Sign in to vote
    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    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