1

I have a database with a primary key on the first column (words) I want to trim the trailing and leading ends of my data, but I get an error that this causes duplicate data

EX: ' David ' will become 'David' and that is already in the database

Is there anyway to compare what I already have in the database to my Select statement (without spaces) and remove any that will cause a duplicate row?

  • Can you ask the database (using a SELECT) if there exists a conflicting row before you issue the INSERT? – usr Feb 25 '15 at 20:32
4
SELECT Func(PK),count(*)
FROM tab
GROUP BY Func(PK)
HAVING Count(*)>1 ;

Where Func() is whatever you're using to clean up the PK column spaces

Example:

SELECT LTRIM(RTRIM(PK)),count(*)
FROM tab
GROUP BY LTRIM(RTRIM(PK))
HAVING Count(*)>1 ;

Example2: (as suggested by Martin in the comments)

WITH cte AS 
(SELECT ROW_NUM() OVER (PARTITION BY LTRIM(PK) ORDER BY LTRIM(PK)) as rn 
FROM tab) 
DELETE FROM cte where rn > 1;
| improve this answer | |
  • Thank You, but how would I then convert it into an update or delete statement? – Dman Feb 25 '15 at 18:16
  • @Dmandy, whats your final objective? The above query will identify which all rows will cause a duplicate, what do you wish to accomplish next? – Akash Feb 25 '15 at 18:17
  • Delete those rows, so that the trim query does not cause any errors – Dman Feb 25 '15 at 18:20
  • So if you have 4 rows 'David',1 ' David',2 'David ',3 and ' David ',4 , you want to delete all 4? – Akash Feb 25 '15 at 18:22
  • 1
    Trailing spaces aren't significant and you don't need the join. The example 2 can be simplified to WITH cte AS (SELECT ROW_NUMBER() OVER (PARTITION BY LTRIM(PK) ORDER BY LTRIM(PK)) as rn FROM tab) DELETE FROM cte where rn > 1; – Martin Smith Feb 25 '15 at 19:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.