SQL to rename a field where it is duplicated

Question

SQL to rename a field where it is duplicated

NadJ

Ten Centuries

Points: 1031
More actions
December 13, 2004 at 10:11 am

#60572

I am looking to adapt the below query to rename a particular field if it is duplicated. It can have up to 10 duplicates therefore the renaming structure is to reflect this.
For example:
ALPHA, ALPHA, BRAVO, DELTA, DELTA, DELTA, DELTA, INDIA, INDIA
These need to be renamed to:
ALPHA, ALPHA-1, BRAVO, DELTA, DELTA-1, DELTA-2, DELTA-3, INDIA, INDIA-1
SELECT column1
FROM database
GROUP BY column1
HAVING count(*) > 1
Can anyone help me get started? Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Hmm. Why exactly do you need to do this?? I'm currently rebuilding an application and every single little structure modification takes hours/weeks to implement. Something like this that could change maybe hundreds of columns names would take me forever to implement.

However this can get you started :

Select C.name as CurrentName, C.name + '-' + cast((Select count(*) from dbo.syscolumns C2 inner join dbo.Sysobjects O2 on C2.id = O2.id and O2.XType = 'U' and C2.id 1) order by NewName

NadJ Ten Centuries Points: 1031 More actions · Answer 2

Thanks Remi, I'm working on getting that to work for me.

My dupes are all in the same table so need to figure out the joins first. Ta

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

That's not possible. A column name must be unique to a table.

The code I gave you scans ALL the columns from all the tables for duplicate field name (excluding views, Sps ...).

However when you start to enter fields like you are trying to do, it means that the design of the table/db is wrong. When there's a need for col1, col2, coln... then theres a need for a second table.

like:

table_customer

fname

lname

phone

adress1

adress2

becomes

table_customer

customer_id

fname

lname

phone

...

table_adress

adress_id

customer_id

street...

and then you can use the customer_id field on both tables to make the inner join and get all the adresses of the customer (like for amazon where you can have multiple shiping adresses)

NadJ Ten Centuries Points: 1031 More actions · Answer 4

Holy moly, I can't believe I made it sound like I have a table with identical columns!!! I'm so sorry, that was poor communication on my part.

Looks like I've exercised your mind in the wrong direction Remi

All I was after was a script to rename ID's if more than one with the same name exists in the table.

ID _______ A random no.______ Say something

ALPHA _______ 1 ____________ ouch

ALPHA _______ 9 ____________ monkey

ALPHA _______ 3 ____________ loo

BRAVO _______ 2 ____________ hello

DELTA _______ 1 ____________ Say

DELTA _______ 4 ____________ something

DELTA _______ 5 ____________ else

INDIA _______ 3 ____________ please

INDIA _______ 7 ____________ home

I'm sorry for having wasted your time.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Then you can tweek the first query I posted, it should give you what you need.