# I'm getting a very strange error in SQL Server. Any Ideas?

**URL:** <https://boards.straightdope.com/t/im-getting-a-very-strange-error-in-sql-server-any-ideas/619072>\
**Category:** Factual Questions\
**Created:** [April 17, 2012, 6:54pm UTC](https://boards.straightdope.com/t/im-getting-a-very-strange-error-in-sql-server-any-ideas/619072 "2012-04-17T18:54:44Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![tdn](https://avatars.discourse-cdn.com/v4/letter/t/94ad74/32.png) [@tdn](https://boards.straightdope.com/u/tdn)\
**Post date:** [April 17, 2012, 6:54pm UTC](https://boards.straightdope.com/t/im-getting-a-very-strange-error-in-sql-server-any-ideas/619072/1 "2012-04-17T18:54:44Z")

</div>

I’ve stripped this down to about as bare bones as it can be:

SELECT fname, minitial, lname,  
CAST(sex as char(1)) as sex  
INTO tblsrp\_PATIENT  
FROM Demographics$

UPDATE tblsrp\_PATIENT SET tblsrp\_PATIENT.sex = ‘M’  
WHERE tblsrp\_PATIENT.sex = 1

UPDATE tblsrp\_PATIENT SET tblsrp\_PATIENT.sex = ‘F’  
WHERE tblsrp\_PATIENT.sex = 2

SELECT \* FROM tblsrp\_PATIENT

DROP TABLE tblsrp\_PATIENT

GO

The reason for this is that Demographics$ stores sex as a numeric value, with the values 1 or 2. After creating the PATIENT table, I want to convert those to character values ‘M’ or ‘F’. If I don’t CAST sex as some sort of char or varchar, the column will be created as a float, and then I can’t update it to ‘M’ or ‘F’.

If I comment out either of the UPDATE statements, everything runs as expected, but of course only one sex gets updated. If I run both of them, I get this error:

Server: Msg 245, Level 16, State 1, Line 1  
Syntax error converting the varchar value ‘M’ to a column of data type int.

It doesn’t matter which of the two UPDATE statements comes first, it always bombs on the first one. Even if I update 1=M and 1=M or 2=F and 2=F (or reversing them), it always bombs on the first one. Even stranger, I took out about 10 previous update statements on other fields. It just bombs on this one. (Or rather, these two.)

Any ideas why?

---

<div class="post-metadata">

**Author:** ![tdn](https://avatars.discourse-cdn.com/v4/letter/t/94ad74/32.png) [@tdn](https://boards.straightdope.com/u/tdn)\
**Post date:** [April 17, 2012, 7:27pm UTC](https://boards.straightdope.com/t/im-getting-a-very-strange-error-in-sql-server-any-ideas/619072/2 "2012-04-17T19:27:00Z")

</div>

Nevermind. Putting the numbers in quotes fixed it.

---

<div class="post-metadata">

**Author:** ![Dervorin](https://avatars.discourse-cdn.com/v4/letter/d/eb8c5e/32.png) [@Dervorin](https://boards.straightdope.com/u/Dervorin)\
**Post date:** [April 17, 2012, 9:33pm UTC](https://boards.straightdope.com/t/im-getting-a-very-strange-error-in-sql-server-any-ideas/619072/3 "2012-04-17T21:33:38Z")

</div>

I was just about to say… you’re updating a field as a char, but comparing it to a number. You could get around the original error and avoid the need for an update by doing something like this:

```auto

SELECT fname, minitial, lname, 
CASE sex 
WHEN 1 THEN 'M'
WHEN 2 THEN 'F' 
END as sex
INTO tblsrp_PATIENT
FROM Demographics$

```

That was, you can create your table with the sex column as char, and it’ll automatically insert the correct value based on the lookup that you provide.

> **[CASE (Transact-SQL) - SQL Server](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-ver16&redirectedfrom=MSDN)**
>
> Transact-SQL reference for the CASE expression. CASE evaluates a list of conditions to return specific results.