CODEWITHSUNDEEP
mysqlsqlconcatenation
Joachim Hollekim
Joachim Hollekim

Reputation: 39

SQL - Remove duplicate in a field

I've updated the names for a number of courses in a school database with the prefix "2015/2016 - ", in order to mark them as belonging to the just finished school year. 

update course
left join courseterm on course.courseID = CourseTerm.courseID
set course.title = CONCAT('2015/2016 - ',`Title`)
where courseterm.termID = 1 or courseterm.termID=2
and course.expires not like '0000-00-00 00:00:00';

Due to a processing error, some 700 of several tens of thousand were run twice. Thus some courses now have the names like "2015/2016 - 2015/2016 - English 101". Any idea how I best go about removing the duplicates?

Upvotes: 0

Views: 37

Answers (3)

Sam
Sam

Reputation: 7303

REPLACE is perfect for accomplishing this.

https://msdn.microsoft.com/en-gb/library/ms186862.aspx

UPDATE course
SET course_title = 
REPLACE(course_title
    ,'2015/2016 - 2015/2016'
    ,'2015/2016 ')
WHERE courseterm.termID = 1 
OR courseterm.termID=2
AND course.expires NOT LIKE '0000-00-00 00:00:00';

Upvotes: 0

osomanden
osomanden

Reputation: 611

I would look for that string "2015/2016 - 2015/2016" in all course.title fields. And replace that string with "2015/2016".

UPDATE course SET title = REPLACE(title, '2015/2016 - 2015/2016', '2015/2016');

I do agree with @Gordon Linoff. Add a field marked as status instead.

If the records are being duplicated - then a simple "DELETE FROM WHERE" can be used.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You can use update again:

update course
    set course_title = substr(course_title, 12)
    where course_title like '2015/2016 - 2015/2016 - %';

As a note: it would be better to just have a flag or status column, rather than changing the name. You may have problems in the future identifying the same course across multiple years.

Upvotes: 1

Related Questions