December 9, 2009 at 11:59 am
Hi,
I have a problem with an update query.
Table1 contains FieldA which can feature leading and/or trailing spaces and may be numeric/non-numeric. I need to update fieldB where the value of FieldA is greater than 3.
UPDATE dbo.Table1
SET
FieldB = FieldA
WHERE
isnumeric(rtrim(ltrim(FieldA))) > '3'
I have tried several methods using the CAST function as well as CASE and IsNumeric statements but I can't achieve the correct results. Any help please?
Thanks in advance,
December 9, 2009 at 12:06 pm
You'll have to test for numeric values first, then do the update.
Because of how SQL Server works, the easiest way to do this (often the only way) is to insert the desired data into a temp table, then use that to build your update statement.
Something like this:
select ID, cast(FieldA as float) as FieldANumeric
into #T
from dbo.Table1
where FieldA not like '%[^0-9]%';
update T1
set FieldB = FieldANumeric
from dbo.Table1 T1
inner join #T T2
on T1.ID = T2.ID
where FieldANumeric > 3;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 10, 2009 at 8:27 am
That's very interesting... (and it works)
Thanks for your reply,
Neal
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply