CODEWITHSUNDEEP
sql-server-2008sql-server-2005
Guru
Guru

Reputation: 11

Update a column in a table

I need to update a column in a table which is of int data type but the values from other table i used for updating is nvarchar. wheather this is achieved if so please let me know.

Upvotes: 1

Views: 108

Answers (3)

aF.
aF.

Reputation: 66687

It could be something like this:

update tablename
set t.column = convert(int, t2.column)
from tablename t
inner join secondtablename t2 on t.column = t2.column
where ISNUMERIC(t2.column) = 1

Upvotes: 2

Nilish
Nilish

Reputation: 1076

CREATE TABLE #t
(
    ID int IDENTITY(1,1),
    Column1 int
)

CREATE TABLE #t1
(
    ID int IDENTITY(1,1),
    Column2 Varchar(50)
)

INSERT INTO #t(Column1)VALUES(1)
INSERT INTO #t(Column1)VALUES(2)

INSERT into #t1(Column2)values('Alpha Numeric')
INSERT into #t1(Column2)values('12')

UPDATE t
SET t.Column1 = t1.Column2
FROM #t t
INNER join #t1 t1 on t.ID = t1.ID
Where ISNUMERIC(t1.Column2) = 1

select * FROM #t

DROP TABLE #t
DROP TABLE #t1

Upvotes: 0

Rob
Rob

Reputation: 2110

If all you data in the nvarchar column are numbers, then, you should be able to do:

update ATable
set intColumn = cast(o.chardata as int)
from ATable a
join OtherTable o on a.tableid=o.tableid

now, you can also put in logic to handle non numeric data with an ISNUMERIC() constraint.

I see aF beat me to the punch.

Upvotes: 0

Related Questions