Puzzling update query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Puzzling update query

Author

Topic

michpaust
Starting Member

21 Posts

Posted - 2005-04-19 : 14:17:26

Me again. I am doing an update and I have the following code

Update
Ar_temp set Ar_temp.inv_num =
CASE
WHEN SUBSTRING([inv_num],1,1)='0' THEN '7'
WHEN SUBSTRING([inv_num],1,1)='1' THEN '7'
end

that throws the following error when I run it

Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Inv_Num', table 'plusdb.dbo.AR_Temp'; column does not allow nulls. UPDATE fails.
The statement has been terminated

The database system that I am pulling this info from is a little quirky and it cuts off the first digit, which is a seven from the inv_num. What i am trying to do is add the seven after the fact but can't seem to quite get there.

Thanks again

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-04-19 : 14:48:20

What about the CASE ELSE ?? This is why the NULL INSERT error is thrown. Also, the way you are writing the CASE will update the entire number to a 7, not append a 7 to the beginning. Is this what you want?

DECLARE @Ar_temp TABLE (inv_num CHAR(5) NOT NULL)
INSERT INTO @Ar_temp
SELECT 12345 UNION SELECT 54321

SELECT inv_num, SUBSTRING([inv_num],1,1)
FROM @Ar_temp

UPDATE @Ar_temp 
SET inv_num =	CASE
			WHEN SUBSTRING([inv_num],1,1) = '0' THEN '7' 
			WHEN SUBSTRING([inv_num],1,1) = '1' THEN '7'
			ELSE inv_num
		END

SELECT inv_num, SUBSTRING([inv_num],1,1)
FROM @Ar_temp

michpaust
Starting Member

21 Posts

Posted - 2005-04-19 : 14:56:29

Nathans, that worked perfectly. I was trying to append the 7 to a number and didn't even think of doing a case else. I've had my head buried in code all day and got stuck on just figuring it out using just the case statement. Sometime it takes another pair of eyes.

Thanks much

michpaust
Starting Member

21 Posts

Posted - 2005-04-19 : 15:01:47

oops, Just checked the Ar_temp table and it replaced the entire number with 7. I just need to append it to the front of a seven difit number. Is there another way I can do this?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-04-19 : 15:22:06

That's priceless...

HTH

=================================================================
Every act of conscious learning requires the willingness to suffer an injury to one's self-esteem. That is why young children, before they are aware of their own self-importance, learn so easily; and why older persons, especially if vain or important, cannot learn at all. -Thomas Szasz, author, professor of psychiatry (1920- )

michpaust
Starting Member

21 Posts

Posted - 2005-04-19 : 15:43:11

Nathans I am soo sorry, I was just so excited that I didn't get an error that I assumed it worked. Now I'm stuck still trying to figure out how to append the 7 and not replace the existing number with a 7. Still asking pitifully for help on this matter

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-04-19 : 16:15:44

... THEN '7' + inv_num

buztaz, dont make me laugh :)

michpaust
Starting Member

21 Posts

Posted - 2005-04-19 : 16:24:30

Thank you nathans. This time I checked and it worked. My first born will be named nathan!

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-04-19 : 16:25:15

and tell us again why the database "cuts off the first digit?" Its a little quirky?
That doesnt sound good.