0

I have two tables, one has foreign keys to the other. I want to delete duplicates from Table 1 at the same time updating the keys on Table 2. I.e count the duplicates on Table 1 keep 1 key from the duplicates and query the rest of the duplicate records on Table 2 replacing them with the key I'm keeping from Table 1. Soundex would be the best option because not all the names are spelled right in Table 1. I have the basic algorithm but not sure how to do it. Help?

So far this is what I have:

declare @Duplicate int
declare @OriginalKey int

create table #tempTable1
(
    CourseID int,   <--- The Key I want to keep or delete 
    SchoolID int, 
    CourseName nvarchar(100),
    Category nvarchar(100),
    IsReqThisYear bit,
    yearrequired int

);

create table #tempTable2
(   
    CertID int, 
    UserID int, 
    CourseID int,   <---- Must stay updated with Table 1
    SchoolID int, 
    StartDateOfCourse datetime, 
    EndDateOfCourse datetime, 
    Type nvarchar(100),
    HrsOfClass float,
    Category nvarchar(100),
    Cost money,
    PassFail varchar(20),
    Comments nvarchar(1024),
    ExpiryDate datetime,
    Instructor nvarchar(200),
    Level nchar(10)


)

--Deletes records from Table 1 not used in Table 2--
delete from Table1
where CourseID not in (select CourseID from Table2 where CourseID is not null)

insert into #tempTable1(CourseID, SchoolID, CourseName, Category, IsReqThisYear, yearrequired)
select CourseID, SchoolID, CourseName, Category, IsReqThisYear, yearrequired from Table1

insert into #tempTable2(CertID, UserID, CourseID, SchoolID, StartDateOfCourse, EndDateOfCourse, Type, HrsOfClass,Category, Cost, PassFail, Comments, ExpiryDate, Instructor, Level)
select CertID, UserID, CourseID, SchoolID, StartDateOfCourse, EndDateOfCourse, Type, HrsOfClass,Category, Cost, PassFail, Comments, ExpiryDate, Instructor, Level from Table2

select cour.CourseName, Count(cour.CourseName) cnt from Table1 as cour
join #tempTable1 as temp on cour.CourseID = temp.CourseID
where SOUNDEX(temp.CourseName) = SOUNDEX(cour.CourseName)  <---

The last part does not exactly work, gives me an error

Error: Column 'Table1.CourseName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

UPDATE: Some of the names in CourseName have numbers in them too. Like some are in romans and numeral format. Need to find those too but Soundex ignores numbers.

Unihedron
  • 10,902
  • 13
  • 62
  • 72
ebolton
  • 1,126
  • 3
  • 14
  • 20
  • I see a lot of objects that are coming out of nowhere here. Where have you defined #tempCourses? How can we tell for sure that the datatype is exactly the same there as it is in Courses, which is also not shown here. Also, what error? – Jeremy Jul 29 '14 at 20:24
  • So sorry I edited the code to be generic Table names and I forgot a few. I added the error – ebolton Jul 29 '14 at 20:31
  • 1
    You need GROUP BY cour.CourseName following the WHERE statement. Anytime you have aggregation, any grouping fields must be contained in GROUP BY. – Jeremy Jul 29 '14 at 20:33

0 Answers0