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.
精彩评论