How to create PK in duplicate column?

Hi,

How to create PK in duplicate column?

for example:

id fname lname age departments

1 aaa aaa NULL NULL
3 aaa bbb NULL NULL
7 aaa b NULL NULL

...

I want to create PK on fname column.

you can't...how would the foreign key know where to link to....row 1 or row 2 or row 3 or all of them?

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Primary Keys are by definition "unique". You cannot have duplicates.

From your example, a index on fname is what you want.|||

Thanks,

So the question becomes "How to delete duplicate records"?

|||

fastest way would be (change realtable to your table name)

select distinct * into backuptable

from realtable

truncate realtable

insert into realtable

select * from backuptable

drop table backuptable

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Hi,

the results are same when using select distinct * from table1

select distinct * from table1

1 aaa aaa NULL NULL
3 aaa bbb NULL NULL
7 aaa b NULL NULL

|||

The answer i gave you won't work because of the id column and the rest is not unique either

what the code below does is get the min id per fname and dumps it into a temp table

then we delete from the real table everything that is not in the temp table

I used min, you can use max

select min(id),fname into #temp
from YourTable
group by fname

delete YourTable
where not exists (select * from #temp where YourTable.id = #temp.id)

Denis the SQL Menace
http://sqlservercode.blogspot.com/

|||

you are only allowed one PK per table! you can have multiple columns as a primary key however.

sql server2000 wrote:

Hi,

How to create PK in duplicate column?

for example:

id fname lname age departments

1 aaa aaa NULL NULL
3 aaa bbb NULL NULL
7 aaa b NULL NULL

...

I want to create PK on fname column.

how to CREATE duplicate rows

this may sound like a weird one, but i need to create duplicates of all rows that satisfy a condition.

using asp, i am able to select rows from a databate using a recordset, only to insert it straight back into the database, thus assigning it a new unique id.

but is there any one to perform this action just using sql?

thanks,

goran.insert your_table
select * from your_table x
inner join (
select field_you_want_to_have_duplicates_on, cnt=count(*)
from your_table group by field_you_want_to_have_duplicates_on
having count(*) > 1) y
on x.field_you_want_to_have_duplicates_on = y.field_you_want_to_have_duplicates_on|||I think I just heard their head explode...

They want to do it from asp...that's a pure sql solution (which is the fastest btw)

Just loop through you're rs and do an insert for every iteration...|||i will disagree
if they truly wanted to do it from asp, why did they come to the sql forum??

they want sql and they know it!! and there's no denying

isnt that right rdjabarov?
rd? rd... RD!
well he HAD my back on this..|||AAAAAAAAAAAAAAAAAAAAAAAAAAAARRRRRRRRRRHHHHHHHH!!!

I'm on 2 conf calls and typing here|||hmmm ... i think you guys got me wrong, my current solution is implemented in asp using the same method that me and you have described. i was seeking a pure SQL solution.

anyway, thanks for replying, i've tried adapting your sql statement to my tables but am not getting very far. let me explain a little further ...

i have a table called 'tblWinesEnPrimeur' and want to make a duplicate of every row in this table that has a 'SectionID' of 21.

thanks,
goran.|||Simply remove all your primary keys and constraints, and your users will surely create the duplicate records for you.|||Originally posted by blindman
Simply remove all your primary keys and constraints, and your users will surely create the duplicate records for you.

Very helpful...

I'm betting you have an IDENTITY Column...ket's say Col1

INSERT INTO tblWinesEnPrimeu (Col2, Col3, Col4, ect)
SELECT Col2, Col3, Col4, ect)
FROM tblWinesEnPrimeu
WHERE SectionId = 21

Without the DDL it's a guess...|||I'd suggest using:INSERT INTO yourTable (collist)
SELECT *
FROM yourTable
WHERE 21 = sectionIdTHis should massively confuse your data, and make the users very happy!

-PatP|||sorry guys, no luck with that either, it's on the client's server and we have no direct access to the database, also the website was orignally designed so that duplicates are not inserted by front-end users of the site.

the sql threw up the same exception as before, simple 'Object required'. like i said it's all workin using the asp/sql combined solution, but the client prefers straight forward sql scripts rather than asp pages which can be run many times and inflate or alter the database.|||I give up...I'm totally lost...

Maybe if you post some (any) code, it might help...|||I'll second that thought! I'd suggest that you use SQL Enterprise Mangler to script everything about that table, the DDL, the indicies, even the permissions. Given that and the nickel description that you posted above, I'll bet that one of us can work out the solution!

-PatP|||insert your_table
select * from your_table x
inner join (
select field_you_want_to_have_duplicates_on, cnt=count(*)
from your_table group by field_you_want_to_have_duplicates_on
having count(*) > 1) y
on x.field_you_want_to_have_duplicates_on = y.field_you_want_to_have_duplicates_on
where x.SectionID = 21