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?

1
  • Can you ask the database (using a SELECT) if there exists a conflicting row before you issue the INSERT?
    – usr
    Feb 25, 2015 at 20:32

1 Answer 1

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;
8
  • Thank You, but how would I then convert it into an update or delete statement?
    – Dman
    Feb 25, 2015 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, 2015 at 18:17
  • Delete those rows, so that the trim query does not cause any errors
    – Dman
    Feb 25, 2015 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, 2015 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; Feb 25, 2015 at 19:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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