UPDATE multiple WHERE SQL not working [closed]

Question

I am trying to run a query to update the DATA table where the MAXNVQ is different it will update a different value into the Burnham Grade column.

This is my code so far:-

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 get an error saying SQL Execution Error:

String or binary data would be truncated the statement has been terminated.

Like I have been a mistake in the SQL

Answer 1

Perhaps this should be a comment, but the comments seem to be about other topics. Your query (or the equivalent) is:

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 are getting a data truncation error, it would be because the strings assigned to Data.BurnhamGrade are too long for the column. So, check the length of the column.

A common reason for this is when the length is left off the variable. So, if your table is defined as:

CREATE TABLE data (
    . . .
    BurnhamData varchar,
    . . .
);

This assigns a default length to the column, which depends on context and is often "1". There is no error, just a column that is shorter than you intend. Instead:

CREATE TABLE data (
    . . .
    BurnhamData varchar(255),
    . . .
);

Or, if this mapping is always true, store the values in a reference table and use a JOIN, Or, use a computed 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 the value up-to-date. It will be correct whenever you query the table and use the column.

Answer 2

As stated in the comments it's likely that the column you are updating (BurnhamGrade) is not big enough to hold the data that you are inserting in to it.

E.g. if your column definition is: BurnhamGrade VARCHAR(10) you wouldn't be able to insert 'Grade II/III' as it's 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

Produces:

Msg 8152, Level 16, State 14, Line 11 String or binary data would be truncated.

Modifying the column specification to: BurnhamGrade VARCHAR(12) allows it to work.

After the change, it produces:

MAXNVQ  BurnhamGrade
1       Grade IV
3       Grade IV
4       Grade II/III

So altering the column definition to a larger value should fix the issue.

One final thing, you can merge 2 of your cases so:

WHEN MAXNVQ = 3 THEN 'Grade IV'
WHEN MAXNVQ < 3 THEN 'Grade IV'

Can use <= 3 as they set the same value, like so:

WHEN MAXNVQ <= 3 THEN 'Grade IV'