Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.
Course Of The Month
1 day, 15 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
SQL Create a new table from a table with a unique combination of two colums
Posted on 2014-11-09
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.
[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
2-
2
- +1
6 Comments
Active today
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
Active today
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
with cte as (SELECT *, ROW_NUMBER() OVER(PARTITION BY ValA, Article) AS rn from MARA_MBEW3
)
SELECT * INTO dupsTable FROM cte WHERE rn > 1
Active 3 days ago
I may have misunderstood your question but would the following do the job ?
SELECT distinct ValA, Article
INTO NEWTABLE
from MARA_MBEW3
SELECT distinct ValA, Article
INTO NEWTABLE
from MARA_MBEW3
Active today
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'
Active today
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
Featured Post
This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses
Course of the Month1 day, 15 hours left to enroll
717 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.