Perhaps not the most efficient, but could you tack a "WHERE NOT EXISTS" to the end of each of your updates within the cursor to ensure the value you are about to update does not already exist?
SOmething like: UPDATE myTable Set x=y where not exists (select y from myTable where x=y) On Tue, May 20, 2014 at 2:25 PM, C. Hatton Humphrey <[email protected]>wrote: > > I have a SQL brain teaser for those who want to play... > > In my database I have a number of tables that contain various columns of > different names that contain identifying references in either a three > character (not digit) or five character (again, not digit) form. This > identifier is used in the three character form to refer to a client. The > five character form refers to all employees from that client. > > I need to change the client code and employee ID's. For conversation's > sake, the old client code is "IAT" and the new one is "1IT". Employee IDs > would begin "AAIAT" and need to be changed to "AA1IT". Furthermore > department codes and some other referencing material begins with the client > code and needs to be changed. > > I know the table and column names I need to hit and even have them set up > in a static union select. In all cases the values begin with either the > client code or employee ID. > > The challenge that I face now is that someone started to set up the 1IT > client code manually while I was working on the script to perform a replace > on the impacted tables. I can't have duplicate values in the system and it > does not always have proper index/constraint rules set up. > > In the least amount of SQL possible, given that I've already set up a > cursor that pulls the table name @TName and column name @CName in from the > afore-mentioned query, how can I write the replace while ensuring that I > don't have a duplicate? > > Until Later! > C. Hatton Humphrey > http://www.eastcoastconservative.com > > Every cloud does have a silver lining. Sometimes you just have to do some > smelting to find it. > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-community/message.cfm/messageid:370309 Subscription: http://www.houseoffusion.com/groups/cf-community/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-community/unsubscribe.cfm
