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
April 24, 2020 at 3:43 pm
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
April 24, 2020 at 3:48 pm
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
April 24, 2020 at 4:02 pm
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
April 24, 2020 at 4:33 pm
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;
April 27, 2020 at 10:02 am
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
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy