CODEWITHSUNDEEP
sql-server
user1336827
user1336827

Reputation: 1826

Update Table records ignoring duplicates

I have a table like so:

id | name | xyID
----------------
1  | xxx  | 100
2  | yyy  | 200
3  | zzz  | 300
4  | zzz  | 200

I need to update all items that are in xyID 200 to now be in xyID = 300 but i have a key on name and xyID so in this case yyy would become 300 but i need to exclude zzz since it would be a duplicate. is there a way i can just ignore duplicates? I suppose i could do this with a script, and select all the items in the previous group and then only update them if they don't exist, but was hoping to just to it in one nice query.

Upvotes: 1

Views: 1099

Answers (1)

SqlZim
SqlZim

Reputation: 38023

This will update 'yyy' but not 'zzz' by using not exists() as part of the where clause to make sure that a row with the same name that already has a record for xyId = 300 does not exist.

update t
  set xyId = 300
where xyId = 200
  and not exists (
    select 1 
    from t as i
    where i.name = t.name
      and i.xyId = 300
      );

If you wanted to delete rows with xyId = 200 that have a corresponding record with xyId = 300 you could use exists() like so:

delete
from t
where xyId = 200
  and exists (
    select 1
    from t as i
    where i.name = t.name
      and i.xyId = 300
      );

rextester demo: http://rextester.com/IIQL1351

results from the update:

+----+------+------+
| id | name | xyId |
+----+------+------+
|  1 | xxx  |  100 |
|  2 | yyy  |  300 |
|  3 | zzz  |  300 |
|  4 | zzz  |  200 |
+----+------+------+

results from the delete:

+----+------+------+
| id | name | xyId |
+----+------+------+
|  1 | xxx  |  100 |
|  2 | yyy  |  300 |
|  3 | zzz  |  300 |
+----+------+------+

Upvotes: 3

Related Questions