Conversion failed when converting the varchar value ' ' to data type int

Question

Conversion failed when converting the varchar value ' ' to data type int

Old Hand

Points: 329

April 24, 2020 at 2:50 pm

Hello, What I am trying to do is to replace the null values that I initially populated my table with using the update statement. I receive the error: 'Conversion failed when converting the varchar value 'THISWILLBETHESTRING' to data type int'. Keep in mind that in the update statement the TEST.ORDERCODE comes from the same table that I am updating. What am I doing wrong here?

--Create sample table for illustration of issue

CREATE TABLE SAMPLETABLE ( 
[Order_ID] int,
[Individual_ID] int,
[SAMPLECOLUMN] varchar(50), 
[FACILITY] int,
[ORDER_CODE] int
) 
GO

--Populate column with values including null for sample column

INSERT INTO 
SELECT 
TABLEX.TABLE_ID AS [Individual_ID],
TABLEX.ORDER_ID AS [Order_ID],
NULL AS [SAMPLECOLUMN],
TABLEX.FACILITY_ID AS [FACILITY],
TABLEX.ORDER_CODE AS [ORDER_CODE]
FROM TABLEX

--UPDATE NULL values of SAMPLECOLUMN with string values 
UPDATE SAMPLETABLE
SET
SAMPLETABLE.SAMPLECOLUMN = CASE WHEN SAMPLETABLE.ORDER_CODE IN ('1') THEN 'THISWILLBETHESTRING'
END
FROM SAMPLETABLE
WHERE SAMPLETABLE.SAMPLECOLUMN IS NULL

This topic was modified 4 years, 7 months ago by Edward Chau.
This topic was modified 4 years, 7 months ago by Edward Chau.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

With no DDL or sample data for TABLEX and TEST, we can but speculate. My strong suspicion is that the TYPE_OF_ORDER column in TEST is int or some other numeric data type, and that's why you can't update it to a string value.

John

jonathan.crawford SSCertifiable Points: 6612 More actions · Answer 2

well, if you're trying to update the samplecolumn field, you don't list that in your update, you're updating the type_of_column (maybe a problem in your sample data?)

Other than that, are you sure that records exist with TEST.ORDERCODE IN ('1')?

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

Edward Chau Old Hand Points: 329 More actions · Answer 3

jonathan.crawford wrote:

well, if you're trying to update the samplecolumn field, you don't list that in your update, you're updating the type_of_column (maybe a problem in your sample data?)
Other than that, are you sure that records exist with TEST.ORDERCODE IN ('1')?

Hi both, My apologies the original post was poorly put together.

I am in fact trying to update SAMPLETABLE.SAMPLECOLUMN in the table that I created which has a data type of varchar(50) and for which I inserted a nulls as the value prior to the update.

--UPDATE NULL values of SAMPLECOLUMN with string values
UPDATE SAMPLETABLE
SET
SAMPLETABLE.SAMPLECOLUMN = CASE WHEN SAMPLETABLE.ORDER_CODE IN ('1') THEN 'THISWILLBETHESTRING'
END
FROM SAMPLETABLE
WHERE SAMPLETABLE.SAMPLECOLUMN IS NULL

This reply was modified 4 years, 7 months ago by Edward Chau.

Edward Chau Old Hand Points: 329 More actions · Answer 4

Here is the DDL. The SQL version is Microsoft SQL Server 2017 (RTM-CU20)

-- DDL and sample data population, start

DECLARE @start TABLE

(ID INT IDENTITY PRIMARY KEY,

data VARCHAR(20) NULL);

INSERT INTO @start ([data])

VALUES

(NULL)

,(NULL)

,(NULL);

-- DDL and sample data population, end

SELECT *

FROM @start;

DECLARE @end TABLE

(ID INT IDENTITY PRIMARY KEY,

data VARCHAR(20) NULL);

INSERT INTO @end ([data])

VALUES

('STRINGVALUEIWANT')

,('STRINGVALUEIWANT')

,('STRINGVALUEIWANT');

-- DDL and sample data population, end

SELECT *

FROM @end;

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

I can't reproduce that. I created SAMPLETABLE from your script, and, since you didn't provide any sample data (only expected results), I inserted the values 1, 1, NULL, 1, 1. Your UPDATE statement ran successfully. I'm on SQL Server 2019, but I'd be surprised if that's the difference.

John