Can I do this in one Update Statement?
-
I want know which exe_name for different cust_name and the same app_category are in the table more than once. For example the first record, abc.exe - ACME - PROD is also for a different customer with the same app_category; abc.exe - BAKER - PROD. So, I want to set multi_flag both as 'Y'.
Existing Data
exe_name.....cust_name.....app_category....multi_flag
abc.exe........ACME..........PROD
def.exe.........ACME..........PROD
abc.exe........ACME..........TEST
def.exe........ACME...........TEST
abc.exe.......BAKER...........PROD
rrr.exe.........BAKER..........PROD
def.exe.......BAKER..........TEST
What I want my data to look like after my update statement
exe_name.....cust_name.....app_category....multi_flag
abc.exe......ACME..........PROD....................Y
def.exe......ACME..........PROD.....................N
abc.exe......ACME..........TEST.....................N
def.exe......ACME..........TEST.....................Y
abc.exe......BAKER.........PROD.....................Y
rrr.exe......BAKER.........PROD.......................N
def.exe......BAKER.........TEST.....................Y
-
Try this
UPDATE t1SET multi_flag='Y'
FROM MyTable t1
WHERE EXISTS (SELECT * FROM MyTable t2
WHERE t2.exe_name=t1.exe_name
AND t2.app_category=t1.app_category
AND t2.cust_name<>t1.cust_name)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
That works great - thank you for helping me out.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply