SQL Create a new table from a table with a unique combination of two colums
Hi
I have a table called "MARA_MBEW3" that has two columns called ValA and Article. I tried to add a primary key
that combines the two columns Article and [ValA] using the SQL syntax below but got the error further down
I now want to create a new table that chops out any records that have a duplicate of these two records. How do I do this
ALTER TABLE MARA_MBEW3
ADD CONSTRAINT pk_MARA_MBEW3 PRIMARY KEY (Article, [ValA])
GO
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'Siris1.MARA_MBEW3' and the index name 'pk_MARA_MBEW3'. The duplicate key value is (EI14528, C921).
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
The statement has been terminated.
I have a table called "MARA_MBEW3" that has two columns called ValA and Article. I tried to add a primary key
that combines the two columns Article and [ValA] using the SQL syntax below but got the error further down
I now want to create a new table that chops out any records that have a duplicate of these two records. How do I do this
ALTER TABLE MARA_MBEW3
ADD CONSTRAINT pk_MARA_MBEW3 PRIMARY KEY (Article, [ValA])
GO
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'Siris1.MARA_MBEW3' and the index name 'pk_MARA_MBEW3'. The duplicate key value is (EI14528, C921).
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
The statement has been terminated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Jim
I tried the following and got the error below
DELETE
FROM MARA_MBEW3 m
JOIN (
SELECT ValA, Article
FROM m
GROUP BY ValA, Article
HAVING COUNT(Article) > 1) dups ON m.ValA = dups.ValA AND m.Article = dups.Article
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'm'.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'dups'
I tried the following and got the error below
DELETE
FROM MARA_MBEW3 m
JOIN (
SELECT ValA, Article
FROM m
GROUP BY ValA, Article
HAVING COUNT(Article) > 1) dups ON m.ValA = dups.ValA AND m.Article = dups.Article
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'm'.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'dups'
I hope you are taking the database backup. If you run Jim's query (by inserting m after the DELETE keyword) you will end up whit a table without the duplicate records. However, there where you had 2 duplicate records will be none. I assume you want to keep at least one record for each duplicate entries
ASKER
Thanks
SELECT distinct ValA, Article
INTO NEWTABLE
from MARA_MBEW3