UPDATE multiple WHERE SQL doesn't work
I am trying to run a query to update a table DATA
where MAXNVQ
is different, it will update a different value in the Burnham Grade column.
This is my code: -
UPDATE Data
SET Data.BurnhamGrade = (CASE WHEN Data.[MAXNVQ] > 3 THEN 'Grade II/III'
WHEN Data.[MAXNVQ] = 3 THEN 'Grade IV'
WHEN Data.[MAXNVQ] < 3 THEN 'Grade IV' END)
WHERE (MAXNVQ > 3) OR
(MAXNVQ = 3) OR
(MAXNVQ < 3)
I am getting SQL Execution error:
String or binary data will be truncated, the statement is terminated.
How was I wrong about SQL
source to share
Perhaps it should be a comment, but the comments seem to cover other topics. Your request (or equivalent):
UPDATE Data
SET Data.BurnhamGrade = (CASE WHEN Data.[MAXNVQ] > 3 THEN 'Grade II/III'
WHEN Data.[MAXNVQ] = 3 THEN 'Grade IV'
WHEN Data.[MAXNVQ] < 3 THEN 'Grade IV' END)
WHERE MAXNVQ IS NOT NULL;
If you get a data truncation error, it will be because the rows assigned Data.BurnhamGrade
are too long for the column. So check the column length.
A common reason for this is that the length is left with a variable. So, if your table is defined as:
CREATE TABLE data (
. . .
BurnhamData varchar,
. . .
);
This assigns a default length to the column, which is context dependent and often "1". No error, just a column that is shorter than you intend. Instead of this:
CREATE TABLE data (
. . .
BurnhamData varchar(255),
. . .
);
Or, if this mapping is always true, store the values in a lookup table and use JOIN
, Or use a calculated column:
ALTER TABLE data
ADD BurnhamGrade AS (CASE WHEN Data.[MAXNVQ] > 3 THEN 'Grade II/III'
WHEN Data.[MAXNVQ] = 3 THEN 'Grade IV'
WHEN Data.[MAXNVQ] < 3 THEN 'Grade IV'
END)
With this approach, you don't have to worry about keeping up to date. It will be right on every table query and column usage.
source to share
As pointed out in the comments, it is likely that the column being updated ( BurnhamGrade
) is not large enough to hold the data that you are inserting into it.
eg. if the column definition is:, BurnhamGrade VARCHAR(10)
you cannot insert 'Grade II/III'
12 characters long.
This recreates the error:
CREATE TABLE #data
(
MAXNVQ INT ,
BurnhamGrade VARCHAR(10)
)
INSERT INTO #data
( [MAXNVQ], BurnhamGrade )
VALUES ( 1, '' ),
( 3, '' ),
( 4, '' )
UPDATE #data
SET #data.BurnhamGrade = ( CASE WHEN MAXNVQ > 3 THEN 'Grade II/III'
WHEN MAXNVQ = 3 THEN 'Grade IV'
WHEN MAXNVQ < 3 THEN 'Grade IV'
END )
-- NOTE THE WHERE CLAUSE ISN'T REQUIRED UNLESS HANDLING NULLS
SELECT *
FROM #data
DROP TABLE #data
Outputs:
Msg 8152, Level 16, State 14, Line 11 String or binary data will be truncated.
Change column specification: BurnhamGrade VARCHAR(12)
lets it work.
After the change, it produces:
MAXNVQ BurnhamGrade
1 Grade IV
3 Grade IV
4 Grade II/III
Therefore, changing the column definition to a larger value should fix the problem.
Finally, you can combine your 2 cases:
WHEN MAXNVQ = 3 THEN 'Grade IV'
WHEN MAXNVQ < 3 THEN 'Grade IV'
Can be used <= 3
as they set the same value:
WHEN MAXNVQ <= 3 THEN 'Grade IV'
source to share
It looks like the data you are trying to put in the field is larger than the size allowed for the field. There is nothing wrong with your SQl at all (except as @sstan pointed out in the comments where clasue is not required).
The problem is what to do about it. You have two choices, and the decision to change things is a business decision, not a programming decision.
You can increase the size of the field to accommodate all possible values. or you can change the possible values to fit into the field size as currently defined.
I think they would have preferred the first solution, but that would require full regression testing of your application and any imports or exports or reports that use the column. However, sometimes the schema does not change, especially if you put the data into a database intended for a third-party application. In this case, you have no choice but to truncate the value to fit in the table.
source to share