CODEWITHSUNDEEP
sqlsql-serversql-update
Harold L. Brown
Harold L. Brown

Reputation: 9956

Update SQL table without creating duplicates

I am using SQL Server and want to update a table with a certain condition. Here is the premise:

Feature:
ID  FeatureName  ApplicationId
1   Car          5
2   Car          6
3   Ship         5

Application:
ID  ApplicationName
5   Mobile
6   Server

Now I want to achieve two things:

  1. If there would be a duplicate entry in the Feature table after the update then delete the old entry.
  2. In the table Feature set ApplicationId to 6 (= Server) where the current ApplicationId is 5 (= Mobile).

So the final Feature table should look like this:

Feature:
ID  FeatureName  ApplicationId
2   Car          6
3   Ship         6

How can I achieve this?

Upvotes: 0

Views: 1564

Answers (4)

piyush jain
piyush jain

Reputation: 113

UPDATE  F
SET  F.ApplicationId=(
case when F.ApplicationId=6 then (SELECT ID FROM  #Application S WHERE ID=5)
ELSE ApplicationId
END )
FROM #Feature F

WITH CTE AS
(
SELECT ID, FeatureName, ApplicationId , ROW_NUMBER() OVER(PARTITION BY FeatureName ORDER BY ApplicationId) rn
FROM #Feature
)

DELETE FROM CTE
WHERE rn>1

SELECT * FROM #Feature

Upvotes: 0

Katerina Gkika
Katerina Gkika

Reputation: 1

Hope this helps.

update feature set applicationid = 6 where applicationid = 5;


delete from feature where id in (
    select min(id)
    from feature
    where featurename in
          (select featurename
          from feature f2 
          group by featurename
          having count(featurename) > 1)
    and applicationId in 
          (select applicationid 
          from feature f3
          group by applicationid
          having count(applicationId) > 1)
    group by featurename, applicationid;
)
;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520938

Update only has a license to modify exsiting records, and it can't remove or add anything normally. I would suggest just doing your update along with a delete query in a single transaction:

UPDATE Feature
SET ApplicationId = 6
WHERE ApplicationId = 5

WITH cte AS (
    SELECT ID, FeatureName, ApplicationId,
        ROW_NUMBER() OVER (PARTITION BY FeatureName, ApplicationId ORDER BY ID) rn
    FROM Feature
)

DELETE FROM cte WHERE rn > 1;

Upvotes: 1

Abhishek Sharma
Abhishek Sharma

Reputation: 132

Try this:

UPDATE Feature SET ApplicationId = 6 WHERE ApplicationId = 5

DELETE FROM Feature
LEFT OUTER JOIN (
   SELECT MIN(ID) as RowId, FeatureName, ApplicationId
   FROM Feature
   GROUP BY FeatureName, ApplicationId
)as KeepRows ON
   Feature.ID = KeepRows.RowId
WHERE
   KeepRows.RowId IS NULL

Upvotes: 1

Related Questions