CODEWITHSUNDEEP
sqlsql-server
TechGuy
TechGuy

Reputation: 4570

Update different data type columns

I have two tables GCB.NewsOne & GCB.NewsTwo both table are same except one column it's GCode in dbo.News table GCode is varchar(100) null and GCB.News table has a bigint null column.

Now I want to update the code in GCode in dbo.News to the value of GCB.News.

I tried like below, but it's not working

UPDATE [GCB].[NewsOne] AS G
SET G.Code = (SELECT P.Code FROM GCB.NewsTwo P WHERE G.ID = P.ID)

Upvotes: 0

Views: 848

Answers (2)

SQL_M
SQL_M

Reputation: 2475

You may not use an alias in an update statement. This works fine:

UPDATE [GCB].[NewsOne] 
SET [GCB].[NewsOne].Code = ( SELECT P.Code FROM GCB.NewsTwo P 
                              WHERE [GCB].[NewsOne].ID=P.ID )

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521534

Try casting the bigint to varchar:

UPDATE G
SET Code = CAST(P.Code AS VARCHAR(MAX))
FROM [GCB].[NewsOne] G
INNER JOIN GCB.NewsTwo P
    ON G.ID = P.ID;

This assumes that your problem really is the types of the two codes, and not something else.

Also note that I rewrote your join using update join syntax, which I think is easier to read.

Upvotes: 2

Related Questions