Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

I have a table personal detail where personUID is
PRIMARY Key

PersId		PersonUID    	PersonnelName
137		01066141L	PREM SINGH  
128		02679425F	RADHAKRISHANAN
129		1066141L	PREM SINGH
130		04457802P	MOTI SINGH  
135		2365847P        Ramesh

In this table I want to update that personuid column which is less than 9 digit. Add with zero in starting but except those no which is already add with zero like Prem singh .

What I have tried:

I have try a simple update query but exception is primary key violation due to Prem singh
Posted
Updated 12-Jan-17 20:12pm
Comments
Maciej Los 13-Jan-17 1:58am    
Can you provide more details about your datatable schema?

Why do you allow duplicate records in a database table. The purpose of the primary key is to prevent such a thing from happening, and it is correct that it is preventing the user form inserting a same person called PREM SINGH more than once. Someone tried to be clever by circumvent this primary key control by inserting this same person but with a missing zero for his PersonID. That results in incorrect data and compromises the integrity of your database. To cut it short, the answer to your situation is to remove that PREM SINGH with a wrong PersonID and do the same to the other similar entries from this dumb hack.
You or whoever the hacker should read up on Introduction to database design[^].
You have better prayed that no other records exist in other tables that link to those users with the hacked-PersonID in this table, else you will run into the foreign key constraint when trying to delete the hacked-PersonID in this table.
 
Share this answer
 
v6
Comments
DINESH K MAURYA 13-Jan-17 2:16am    
Yes Peter you are right, thanks alot
I follow the link which you provide, so pls let me know how to find the this(Pram singh) type of record.
Maciej Los 13-Jan-17 2:16am    
Peter,
If i understand OP correctly, there's no duplicate data. There's a problem in update query, which tries to update even proper data in PersonUID field. Please, see my answer.
Peter Leow 13-Jan-17 2:56am    
Noted.
Maciej Los 13-Jan-17 4:25am    
I decided to upvote (+5) your answer, because it contains very good advice as to the database design.
Take a look at example:

SQL
--create variable - type of table
DECLARE @tmp TABLE(PersId INT, PersonUID VARCHAR(9) PRIMARY KEY, PersonnelName VARCHAR(30))

--insert sample data into table
INSERT INTO @tmp (PersId, PersonUID, PersonnelName)
VALUES(137, '01066141L', 'PREM SINGH'), 
(128, '02679425F', 'RADHAKRISHANAN'),
(129, '1066141L', 'PREM SINGH'),
(130, '04457802P', 'MOTI SINGH'),
(135, '2365847P', 'Ramesh')

--update data
UPDATE t1 SET PersonUID = RIGHT('000000000', 9-LEN(t1.PersonUID)) + t1.PersonUID
FROM @tmp AS t1 INNER JOIN  
(
	SELECT PersId, PersonUID, ROW_NUMBER() OVER(PARTITION BY RIGHT('000000000', 9-LEN(PersonUID)) + PersonUID ORDER BY LEN(PersonUID) DESC) AS PID, PersonnelName 
	FROM @tmp 
) AS t2 ON t1.PersId = t2.PersId AND t2.PID = 1

--show data after apdate
SELECT *
FROM @tmp


Result:
137	01066141L	PREM SINGH
128	02679425F	RADHAKRISHANAN
129	1066141L	PREM SINGH   --skipped
130	04457802P	MOTI SINGH
135	02365847P	Ramesh       --added leading zeros


I hope that above example is helpful in understanding how to update your data.

[EDIT]

I have used ROW_NUMBER()[^] function to detect which record is duplicated. ROW_NUMBER() function returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
 
Share this answer
 
v2
Comments
DINESH K MAURYA 13-Jan-17 2:21am    
sir,
I want to update skip those record which is already zero and rest of record which is less than 9 update with zero. personuid is primary key.
thanks in advance.
Maciej Los 13-Jan-17 2:23am    
And does my code doesn't meet your criteria? What is expected output?
DINESH K MAURYA 13-Jan-17 2:26am    
PersId		PersonUID    	PersonnelName
137		01066141L	PREM SINGH  
128		02679425F	RADHAKRISHANAN
129		1066141L	PREM SINGH
130		04457802P	MOTI SINGH  
135		02365847P        Ramesh


only Ramesh is update..
or which is less than 9 except the slimier with zero
I have 5K record in which some of similer with zero and some is less than 9.
Maciej Los 13-Jan-17 2:30am    
Take a look at my result set. My query changes PersonUID for Prem Singh (PersID=129) and adds a leading zero. Am i right?
DINESH K MAURYA 13-Jan-17 2:36am    
Sir,
If i update 1066141L PREM SINGH which is already with add with zero see persid=137, If i update persid =129 then erros msg goes primary key violence.
So when update then check that another no in this column is add with zero or not if it have then skip other wise update with zero.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900