Gazi Enosis Gazi Enosis - 4 months ago 8
SQL Question

Update multiple column value from one column of the same table

In My table there have two column.
Table structure is:

Combined's data type is varchar(50) and First's data type is int.

ID--Combined -----------First

1---1.2.3.4-------------- null

2---3.8.6.9-------------- null

I want to convert

ID---Combined -----------First

1---1.2.3.4-------------- 1

2---3.8.6.9-------------- 3

I wrote this lines of code


UPDATE dbo.Test

SET dbo.Test.First = CAST(SUBSTRING(T.Combined,1,1) AS INT)

FROM dbo.Test as T

WHERE T.ID = dbo.Test.ID;


But my code gives that error '
The multi-part identifier "dbo.Test.ID" could not be bound.
'

Answer

You don't need the update-from syntax here. Since you want your First column to relate to the Combined column of the same row, a simple update statement will do:

UPDATE dbo.Test
SET    dbo.Test.First = CAST(SUBSTRING(dbo.Test.Combined, 1, 1) AS INT)