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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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!
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply