CODEWITHSUNDEEP
sql
user2275330
user2275330

Reputation: 3

SQL Update on same table and Preventing Duplicates

I am trying to update the values on a table which might have duplicates and I want to avoid those duplicates to avoid the errors:

Table:

TicketID : UserID
  0      :   1
  0      :   5
  0      :   6
  1      :   4
  1      :   5

What I am trying to do is update all the Ticket Id's that = 0, to the value of 1. As you can see there is already a value of 1 : 5 which causes the conflict. I have tried merge, insert into with where and I can not get a query that ignores updating rows that will potentially exist.

After the update I would manually delete the remaining 0 : 5 since that would not of been updated, I thought merge could do that for me but I could not get that to work

Upvotes: 0

Views: 1306

Answers (2)

Pandian
Pandian

Reputation: 9136

Try like below. It will help you..

UPDATE TABLENAME
SET TicketID = 1
WHERE TicketID = 0 AND UserID Not IN(SELECT distinct UserID FROM TABLENAME 
WHERE TicketID=1)

DELETE FROM TABLENAME WHERE TicketID = 0

Upvotes: 0

Pirion
Pirion

Reputation: 519

Check for duplicates first and delete them - and then perform the ubdate.

BEGIN TRAN

DELETE FROM Table
WHERE TicketID = 0 AND UserID IN(SELECT UserID FROM TABLE WHERE TicketID=1);

UPDATE TABLE
SET TicketID = 1
WHERE TicketID = 0;

/*If no error run*/
COMMIT TRAN
/*If error run*/
ROLLBACK TRAN

Upvotes: 1

Related Questions