Solved

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

Posted on 2014-11-09
6
118 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:murbro
6 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
Comment Utility
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 24

Assisted Solution

by:chaau
chaau earned 250 total points
Comment Utility
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
Comment Utility
I may have misunderstood your question but would the following do the job ?

SELECT distinct ValA, Article
INTO NEWTABLE
from MARA_MBEW3
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:murbro
Comment Utility
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 24

Expert Comment

by:chaau
Comment Utility
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:murbro
Comment Utility
Thanks
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now