locked
update a table from table RRS feed

  • Question

  • hello all,

    I'm trying to update a field in table1 from table2 and the fields are of different types.

    one is a varchar and the other is a float so I have to cast but everything I try fails.

    anyone have any ideas?

    Tuesday, April 9, 2013 8:09 PM

Answers

  • IMHO,20E is not valid float type..i suggest you better fix the data types first.....

    Hope it Helps!!

    • Marked as answer by Unwind_1 Wednesday, April 10, 2013 1:30 PM
    Wednesday, April 10, 2013 12:46 PM
    Answerer

All replies

  • hello all,

    I'm trying to update a field in table1 from table2 and the fields are of different types.

    one is a varchar and the other is a float so I have to cast but everything I try fails.

    anyone have any ideas?

    Please let us know the table structure and some dummy data to test.

    this is definately possible.


    Sarabpreet Singh Anand

    SQL Server MVP Blog , Personal website

    This posting is provided , "AS IS" with no warranties, and confers no rights.

    Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Tuesday, April 9, 2013 8:20 PM
  • Please try this one, i know we can do better than this but its just a test, let me know if you have more questions.

    create table t1(
    tid int identity(1,1),
    tname varchar(40)
    )
    go
    
    create table t2(
    tid int,
    sub_name varchar(20),
    tint float
    )
    go
    
    insert into t1(tname) values('hi')
    go 9
    
    insert into t2 values(2,'Robert',4.23)
    insert into t2 values(4,'Smith',6.23)
    insert into t2 values(6,'Paul',42.3)
    insert into t2 values(1,'Barbara',13.09)
    
    update t1
    set tname =
     (select convert(varchar(20),tint) from t2 where tid = 6)
     where tid = 6


    Sarabpreet Singh Anand

    SQL Server MVP Blog , Personal website

    This posting is provided , "AS IS" with no warranties, and confers no rights.

    Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Tuesday, April 9, 2013 8:27 PM
  • Here’s what I have and I’m using sql2008.
    T1 field called grade (float, null)
    T2 field called grade (nvarchar(255, null)
    I’m trying to update t1.field from t2.field where t1.empID = t2.empID
    It sounds simple but everything I try fails.

    thanks

    Wednesday, April 10, 2013 11:16 AM
  • see sample script below..if your t2 grade column has data, that is not supported by float data type, those will fail. I added isnumeric clause to check this but ISnumeric has limitations...so, the error must be happening because of your data. So, if possible make both the columns of the same data type. it's makes life a lot easier....

    create table t11_a (Grade float,id int)
    create table t22_b (grade nvarchar(255),id int)
    
    insert into t22_b (Grade,ID)
    values (1.00,1),(2.34,2),(3.00,3)
    
    insert into t11_a(ID)
    values (1),(2),(3)
    
    select * from t11_a
    Select * from t22_b
    
    update t11_a
    set Grade = t22_b.grade 
    from t11_a inner join t22_b
    on t22_b.id = t11_a.id
    where ISNUMERIC(t22_b.grade)=1
    
    select * from t11_a
    Select * from t22_b
    
    -- if on sql 2008 and above, you can try the below ---
    Merge t11_a 
    Using (select * from t22_b) as t22_b on t22_b.id=t11_a.id 
    When Matched  and ISNUMERIC(t22_b.grade)=1 then update set grade=t22_b.grade ;
    
    select * from t11_a
    Select * from t22_b
    
    drop table t11_a
    drop table t22_b


    Hope it Helps!!

    Wednesday, April 10, 2013 11:51 AM
    Answerer
  • this runs but it doesn't update:

    Merge t11_a
    Using (select * from t22_b) as t22_b on t22_b.id=t11_a.id
    When Matched  and ISNUMERIC(t22_b.grade)=1 then update set grade=t22_b.grade ;

    the grade field looks like 20E or 058 and some are null


    Wednesday, April 10, 2013 12:42 PM
  • IMHO,20E is not valid float type..i suggest you better fix the data types first.....

    Hope it Helps!!

    • Marked as answer by Unwind_1 Wednesday, April 10, 2013 1:30 PM
    Wednesday, April 10, 2013 12:46 PM
    Answerer