CODEWITHSUNDEEP
sqlsql-server-2012
NoBullMan
NoBullMan

Reputation: 2188

Replace and delete column values in SQL Server database

I have a table that contains user id and course id. It was decided that some of the courses are actually duplicates and I need to get rid of them. So, this means I have to replace one of the course ids with the other but make sure there are no duplicates.

Here is an example:

UserID    CourseID
------    --------
1         1
1         2
1         3
1         4
2         1
2         2

They decided course ids 1 and 3 are the same, so need to get rid of 1 and replace with 3 and end up with:

UserID    CourseID
------    --------
1         2
1         3
1         4
2         3
2         2

So, for user ID 2 is just an update of course id but for user id 1 it is an update and a delete. there is unique constraints in this table for course id so can't update all 1s to 3 and remove duplicates.

Upvotes: 1

Views: 794

Answers (2)

simon at rcl
simon at rcl

Reputation: 7344

Start by deleting those that would make duplicates:

delete uc1
from UserCourse uc1
inner join UserCourse uc3 on uc3.UserId = uc1.UserId
where uc1.CourseId = 1 and uc3.CourseId = 3

Then, just do the update:

update UserCourse set CourseId = 3 where CourseId = 1

Upvotes: 1

S3S
S3S

Reputation: 25132

I'd do this in two stages... first update.

update table 
set CourseID = 3 
where CourseID = 1

Then delete the dupes

with cte as (
select *, row_number() over (partition by UserID, CourseID order by CourseID) RN
)


select * from cte where RN <> 1
--the below command will delete what is returned from the select
--delete from cte 
--where RN <> 1

Upvotes: 0

Related Questions