We help IT Professionals succeed at work.

SQL Create a new table from a table with a unique combination of two colums

Murray Brown
Murray Brown asked
on
174 Views
Last Modified: 2014-11-14
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.
Comment
Watch Question

SQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
I may have misunderstood your question but would the following do the job ?

SELECT distinct ValA, Article
INTO NEWTABLE
from MARA_MBEW3
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
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'
CERTIFIED EXPERT
Top Expert 2013

Commented:
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
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thanks
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.