How can I rename a value if it is a duplicate? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How can I rename a value if it is a duplicate?

Author

Topic

cutlass1972
Starting Member

12 Posts

Posted - 2011-01-14 : 13:06:46

I am trying to copy a column to another column. the column I am copying to has a constraint that will not allow duplicate values, however the column I am copying from does, and duplicates do exist.

I can find the duplicates easy enough

select userid,count(*) from database.schema.table group by userid having count(*)>1

there are only doubles, no triples, etc.. I would like to rename the duplicates to "username2"

This is what I have so far

insert into database.schema.table.userid
select userid+'2', COUNT(*) from database.schema.table group by userid having COUNT(*)>1

obviously it is not working. Can anyone assist?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-14 : 13:26:04

Assuming userId is a character data type

With users (userId, id)
AS (
	SELECT userId, row_number() over(partition by userId order by userId) id from TABLE1
)
INSERT	TABLE2 (userId)
select	userID +
	CASE id
		WHEN 1 THEN ''
		ELSE Convert(char(1), id)
	End
from users

cutlass1972
Starting Member

12 Posts

Posted - 2011-01-14 : 13:46:13

I am going to have to study that script, I do not understand it.

Just to clarify, "userid" is the column name. Does your script still apply?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-14 : 14:01:09

You didn't tell us what data types the columns are, so Russell made a guess. There is probably going to be some implicit conversions going on which may or may not work depending on the data types involved. If you want to provide those types, we can tell you if the script will work or if it needs to be adjusted. For reference it's always a good idea to post your DDL so we know the types and constraints of your data. Here is a link that might come in handy for future posts:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-14 : 14:06:39

^ What he said

If your userId column is character type it will work. If it is an integer type, we'll need to cast it. I guessed based on your sample in the 1st post.

If you execute it with the INSERT commented out, you can see what it is doing. Basically, finding all duplicates and appending a number to the dupes, leaving the originals alone.

With users (userId, id)
AS (
	SELECT userId, row_number() over(partition by userId order by userId) id from TABLE1 -- <-- your table name here
)
--INSERT	TABLE2 (userId)
select	userID +
	CASE id
		WHEN 1 THEN ''
		ELSE Convert(char(1), id)
	End
from users

cutlass1972
Starting Member

12 Posts

Posted - 2011-01-14 : 14:18:14

ok, I will play with it and see if I can get it to work

here is the character type on the column in question

[USERID] [nvarchar](64) NULL,

Guys, thanks for helping a NEWB out. I know it can be frustrating trying to help someone that is just starting out.

cutlass1972
Starting Member

12 Posts

Posted - 2011-01-14 : 14:30:29

ok I tried it and am getting a syntax error. here is what I am trying to execute

(table name is dbusers)

With users (userid, id)
AS (
SELECT userid, row_number() over(partition by userid order by userid) id from dbo.dbusers
)
INSERT TABLE2 (userid)
select userid +
CASE id
WHEN 1 THEN ''
ELSE Convert(char(1), id)
End
from users

the syntax error states

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'users'.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-14 : 14:31:52

Are you using SQL Server 2000 by chance?

cutlass1972
Starting Member

12 Posts

Posted - 2011-01-14 : 14:32:28

I meant table name is userid not dbusers

cutlass1972
Starting Member

12 Posts

Posted - 2011-01-14 : 14:33:01

quote:
Originally posted by russell

Are you using SQL Server 2000 by chance?

no 2008 r2

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-14 : 14:39:55

Code works fine. Do you have stray characters somewhere? Is what you posted the exact query? And is it the only thing in the batch? Did you comment out the INSERT TABLE2 part (assuming that TABLE2 isn't the name of your other table)?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-14 : 14:49:00

Table name is userId? What is the name of the column you're searching?

cutlass1972
Starting Member

12 Posts

Posted - 2011-01-14 : 15:00:44

ok, I changed some of the names trying to make it simpler to express here. I think that is my problem, I apologize for complicating things.

the actual table name is "cenlck"

the actual column name is "userdefined2"

here is the actual script I am trying to run

begin transaction

With users (USERDEFINED2, id)
AS (
SELECT USERDEFINED2, row_number() over(partition by USERDEFINED2 order by USERDEFINED2) id from dbo.cenlck
)
INSERT USERDEFINED2
select USERDEFINED2 +
CASE id
WHEN 1 THEN ''
ELSE Convert(char(1), id)
End
from users

here is the error

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'users'.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-14 : 15:13:03

What is the name of the column you're copying to?

Actually, let's see the table definition for dbo.cenlck including primary key (if one exists).

Also, I gave you an insert query. You need an Update query. My bad.

And why the Begin Tran?

cutlass1972
Starting Member

12 Posts

Posted - 2011-01-14 : 16:32:21

I am not trying to copy to a table I just want to rename the duplicate within the table so that for example there are 2 of the same value (value, and value) it will add a "2" to the second one (value and value2)

I used the begin transaction so I could roll back or comit, just for testing the script purposes, in case I did something to the data I didn't mean to.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-14 : 17:47:06

So what you really want is a select statement that will produce a rownumber unique to the UserIDs? Try the below to select the data to see if it is what you want

--return the records
SELECT USERDEFINED2,
ID = row_number() over(partition by USERDEFINED2 order by USERDEFINED2)
FROM Users

You could use that select to insert into another table

Poor planning on your part does not constitute an emergency on my part.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-01-14 : 18:42:55

The WITH keyword has a specific requirement, or you will get that error. You have to end the previous statement correctly for SQL Server to understand that you are creating a common table expression. The semi-colon terminates (ends) a SQL statement - so, you are getting your error because you don't have that.

Try the following:

Begin Transaction; -- note ';' terminates this sql statement

With users (USERDEFINED2, id)
AS (
SELECT USERDEFINED2, row_number() over(partition by USERDEFINED2 order by USERDEFINED2) id from dbo.cenlck
)
INSERT USERDEFINED2
select USERDEFINED2 +
CASE id
WHEN 1 THEN ''
ELSE Convert(char(1), id)
End
from users; -- note ';' terminates this sql statement

Rollback Transaction; -- roll everything back until fully tested

Have you tested your select statement to make sure it is returning the expected results? If not, comment out the insert statement and see what is being returned.

Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources