October 2, 2014 at 5:01 am
I am trying to correct linenumbers in a certain entry (because I deleted a part of it) and created the query for this:
update tablename set lineno=lineno- 12, sysmodified=getdate()
where 1=1
and entry=545554
and type='X'
and lineno>=568
But when I do this all lines after lineno 568 will get values from 1000, 1001, 1002, etc.???
If I put it in a select then it works fine. How come?
October 2, 2014 at 5:40 am
What's the datatype of LineNo?
October 2, 2014 at 5:55 am
Hi Phil,
Thanks for your reply. The datatype of the field lineno is char(4), null
KR
October 2, 2014 at 6:06 am
marc.eilander 13301 (10/2/2014)
Hi Phil,Thanks for your reply. The datatype of the field lineno is char(4), null
KR
So there's your problem. It's non-numeric. You need something more like this (untested):
update tablename
set lineno = cast(cast(lineno as int) - 12 as char(4)), sysmodified=getdate()
where 1=1
and entry=545554
and type='X'
and cast(lineno as int) >=568
October 2, 2014 at 6:25 am
Thanks, but unfortunately that does change the output. 'Funny' thing is that when I run the query
update tablename set lineno=lineno- 12, sysmodified=getdate()
where 1=1
and entry=545554
and type='X'
and lineno=568 -- so this line has been changed from bigger and equal then, to equal then
then it does work? But I do not want to update per line ofcourse.
October 2, 2014 at 6:42 am
I think I misplaced a bracket. How about this?
update tablename
set lineno = cast((cast(lineno as int) - 12) as char(4)), sysmodified=getdate()
where 1=1
and entry=545554
and type='X'
and cast(lineno as int) >=568
October 2, 2014 at 7:33 am
It took some effort and with some help from colleague I came to this fix:
BEGIN TRAN
UPDATE TABLENAME
SET lineno=
(case when len(lineno -12) = 1 then ' '+cast((cast(lineno as int) - 12) as char(1)) else
(case when len(lineno -12) = 2 then ' '+cast((cast(lineno as int) - 12) as char(2)) else
(case when len(lineno -12) = 3 then ' '+cast((cast(lineno as int) - 12) as char(3)) else
(case when len(lineno -12) = 4 then cast((cast(lineno as int) - 12) as char(4)) end) end) end) end)
WHERE 1=1
and entry=554545
and type='X'
and cast(lineno as int) >=567
COMMIT
This fixes it. Thank you for helping!
October 2, 2014 at 7:40 am
Well, I'm glad it worked. But the mixture of char data types and arithmetical operations makes me find it difficult to believe that that was what you wanted!
October 2, 2014 at 1:37 pm
If it's varchar, best would be to test it as varchar, thus not converting the column ... but that is only safe if all values are consistently left-padded with zero(s).
Either way, the SET below is much easier to read/maintain.
UPDATE tablename
SET [lineno]=RIGHT('0000' + ([lineno]- 12), 4), sysmodified=getdate()
WHERE
1=1
and entry=545554
and type='X'
and [lineno]>='0567' --or 567 if the values are not consistent
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply