VaMEEK05

1CREATE TABLE table (
2  id varchar(50), key varchar(64) NOT NULL, value varchar(64) NOT NULL, ...
3  CONSTRAINT pk_table_id PRIMARY KEY CLUSTERED (id ASC) WITH (...) ON [PRIMARY],
4  CONSTRAINT pk_key_value UNIQUE NONCLUSTERED ( key ASC, value ASC) WITH (...) ON PRIMARY
5) ON [PRIMARY]
6	
7UPDATE table
8SET key = 'newKey'
9WHERE key = 'oldKey'
10	
11-- 1st try, nothing is updated
12IF NOT EXISTS (SELECT * FROM table WHERE key='newKey' AND value IN (SELECT value FROM table WHERE key='oldKey'))
13BEGIN
14  UPDATE table
15  SET key = 'newKey'
16  WHERE key = 'oldKey'
17END
18
19-- 2nd try, exception thrown
20UPDATE table
21SET key = 'newKey'
22WHERE key = 'oldKey' AND
23  (SELECT COUTN(*) FROM table WHERE key='newKey' AND value IN (SELECT value FROM table WHERE key='oldKey')) > 0
24
25-- 3rd try, nothing is updated
26UPDATE table
27SET key = 'newKey'
28WHERE NOT EXISTS (SELECT * FROM table WHERE key='newKey' AND value IN (SELECT value FROM table WHERE key='oldKey'))