set primary key `id`

Table:

ProductComapny

Fields:id,name

ProductComapny content 10000 recorde.

now i want id set primary key But a number of id recordings have been repeated

1.how can id set primary key???

2.how can remove duplicate recorde?

3.How can I get the number of duplicate records?

开发者_JAVA技巧BY SQL In SqlServer And MySql

For Study

---

Answered for SQL Server - This won't work in MySQL. Please specify which, rather than patronising people who are able to help you.

To get the count of duplicates:

SELECT id, name, COUNT(*) FROM productCompany GROUP BY id, name

To apply a primary key, first delete the duplicates as follows:

WITH sorted AS
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY id, name) AS duplicate_id,
    id,
    name
  FROM
    productCompany
)
DELETE
  sorted
WHERE
  duplicate_id > 1

Then use Management Studio to apply your primary key.

---

3.How can I get the number of duplicate records?

SELECT id, COUNT(*)
    FROM ProductCompany
    GROUP BY id
    HAVING COUNT(*) > 1

2.how can remove duplicate records?

How would you choose which one to keep?

---

If your question is about MySQL, then this will do:

ad 3: SELECT COUNT(*),id FROM ProductComapny GROUP BY id will give you a count of occurences of each id in the database (btw "ProductCom ap ny"?)

ad 2: that depends on your business logic - which do you want to keep?

ad 1: ALTER TABLE ProductComapny ADD PRIMARY KEY(id);

(yes, the MySQL/MSSQL difference matters: IIRC neither of the above is valid code in MSSQL)

---

if you force mysql to add a primary key, it going to remove all duplicate to be able to succsed, you can force a alternatetion of a table whit the keyword ignore, that ignore warnings.

ALTER IGNORE TABLE ProductComapny ADD PRIMARY KEY (id);

---

One way to remove duplicate entries is to copy them into temp table, delete all duplicates from main table, copy data from temp to main.

insert into TempTable
select id, name
from ProductComapny
group by id, name
having count(*) > 1

delete from ProductComapny
where id in (select id from TempTable)

insert into ProductComapny
select id, name
from TempTable

This should work.
After that you can set primary key on ID field since there will be no more duplicates.