• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 769
  • Last Modified:

SQL 2000 syntax add leading zeros to varchar

I am trying to add leading zero's to a column with this update query:
use Kronos
update p
   set p.personnum = Right('0000000000' + [badgenum],10)
  from person P
  join Badgeassign b
    on p.personid = b.personid
But I get this error:
Server: Msg 2601, Level 14, State 3, Line 2
Cannot insert duplicate key row in object 'PERSON' with unique index 'XU1_PERSON'.
The statement has been terminated.
What am I doing wrong ?
0
Ryedog
Asked:
Ryedog
  • 7
  • 4
  • 2
  • +2
1 Solution
 
zadeveloperCommented:
you are ot doing anything wrong (syntax-wise), but you have a unique constraint on field personnum and you are trying to update this row , where another row exists having the new id you are trying to use.
0
 
zadeveloperCommented:
try
print Right('0000000000' + [badgenum],10)  when you get the error and the search Person where Personnum = result. perhaps some of [badgenum] is being truncated (which out the sample data I wont know, but you can try

select distinct len(badgenum) from Badgeassign where len(badgenum) > 10
hopefully you should not get any results, if you do there is a truncation happening and you could get a senario like: 12345678901 and 12345678902 would both try update personnum with 1234567890
0
 
RyedogAuthor Commented:
How can I get around this ? Perhaps if I first remove any existing leading zero's then add the required leading zero's ? Although I'm unsure how to remove the existing leading zero's. Any suggestions ?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
RyedogAuthor Commented:
I'm sorry, I think we crossed comments. Any way the select distinct didn't return anything from badgenum. There are quite a few entries in the personnum field with leading Zero's and 10 digits. I 'm thinking if I remove the existing leading zero's, the new update may work. Sound right ?
0
 
SharathData EngineerCommented:
Need more details.

1) Do you have index on personnum column? What is the data type and data in this column?
2) Do you have duplicate records for badgenum column in Badgeassign table?
0
 
RyedogAuthor Commented:
How can I tell if I have an Index on the column ?  The data type is varchar in both columns and the data in badgenum is numeric, the first 15 rows in the personnum column are alpha the rest are numeric.
how can I tell if there are duplicate records in the badgeassign table ?
0
 
js-profiCommented:
the Right('0000000000' + [badgenum], 10) always results in '0000000000' which is duplicate for second update record.Use

   set p.personnum = [badgenum]

to firstly remove the leading zeros.

Then

    set p.personnum = Left('0000000000', 10-p.personnum.length) + p.personnum

to add the leading zeros.



   
0
 
zadeveloperCommented:
Actually both methods work just fine and both have the same down fall (when personnum lenght is greater than 10 both will give incorrect answers) try:

set p.personnum = case when len(badgenum ) > 9 then badgenum else  Right('0000000000' + [badgenum],10)  end

or alternatively you could also use:

set p.personnum = case when len(badgenum ) > 9 then badgenum else  Left('0000000000', 10-p.personnum.length) + p.personnum end


if you get an error which says data will be truncated then you have data which exceeds the char limit of the field, and you will need to increase that.
0
 
RyedogAuthor Commented:
I get this error:  Server: Msg 107, Level 16, State 2, Line 2
The column prefix 'p.personnum' does not match with a table name or alias name used in the query.


when running : set p.personnum = Left('0000000000', 10-p.personnum.length) + p.personnum

0
 
zadeveloperCommented:

use Kronos
update p
   set p.personnum = case when len(badgenum ) > 9 then badgenum else  Right('0000000000' + [badgenum],10)  end
  from person P
  join Badgeassign b
    on p.personid = b.personid

Open in new window

0
 
jamesguCommented:
you may run below queries to find out why there were duplicates, then figure out a solution based on that
-- 1. to get duplicates
with cte as (
select p.personid, Right('0000000000' + [badgenum],10) as new_badgenum
  from person P
  join Badgeassign b
    on p.personid = b.personid
)
select new_badgenum from cte
group by personid
having count(distinct personid ) > 1

-- to find personid
select * from Badgeassign
where Right('0000000000' + [badgenum],10)
= <pick one from the above result>

Open in new window

0
 
SharathData EngineerCommented:
To find the duplicate badge numbers in Badgeassign table, execute this query and check the result.

select badgenum from Badgeassign group by badgenum having count(badgenum) > 1;
0
 
RyedogAuthor Commented:
Sorry all, I'm currently slammed with a client disaster. I will try your soloutions at the very first opportunity I have. I appreciate you knowledge and time, and again I appologize for the hang up.
0
 
js-profiCommented:
the p. was from your own update statement where i only posted the set clause.
0
 
RyedogAuthor Commented:
zadeveloper's solution : use Kronos
update p
   set p.personnum = case when len(badgenum ) > 9 then badgenum else  Right('0000000000' + [badgenum],10)  end
  from person P
  join Badgeassign b
    on p.personid = b.personid

worked perfectly. Thank you.

Thank you all very much
0
 
RyedogAuthor Commented:
Worked perfectly thank you
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 7
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now