?
Solved

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

Posted on 2014-11-09
6
Medium Priority
?
137 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.
0
Comment
Question by:Murray Brown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 1000 total points
ID: 40431575
DELETE 
FROM MARA_MBEW3 m
  JOIN (
      SELECT ValA, Article
      FROM YourTable
      GROUP BY ValA, Article
      HAVING COUNT(Article) > 1) dups ON m.ValA = dups.ValA AND m.Article = dups.Article

Open in new window

btw Might not be a bad idea to INSERT these into some kind of backup table, if for some reason you need them later.
0
 
LVL 25

Assisted Solution

by:chaau
chaau earned 1000 total points
ID: 40431639
To create a new table without duplicates you can use the following statement:
with cte as (SELECT *, ROW_NUMBER() OVER(PARTITION BY ValA, Article) AS rn from MARA_MBEW3
)
SELECT * INTO newTable FROM cte WHERE rn = 1

Open in new window

If you wish to store the duplicate records for later analysis use this statement:
with cte as (SELECT *, ROW_NUMBER() OVER(PARTITION BY ValA, Article) AS rn from MARA_MBEW3
)
SELECT * INTO dupsTable FROM cte WHERE rn > 1

Open in new window

0
 
LVL 2

Expert Comment

by:alcindor
ID: 40431726
I may have misunderstood your question but would the following do the job ?

SELECT distinct ValA, Article
INTO NEWTABLE
from MARA_MBEW3
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:Murray Brown
ID: 40432006
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'
0
 
LVL 25

Expert Comment

by:chaau
ID: 40432011
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
0
 

Author Closing Comment

by:Murray Brown
ID: 40444136
Thanks
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question