I worked with a SQL DBA many years ago who would always tell me: "There is ALWAYS a way to do it set based". And true to form, every problem I presented him with, he found a set based solution. Now, some of them got pretty nasty. But it could be done. YOurs sounds like a one-time-thing though, so not worth the extra effort.
On Wed, May 21, 2014 at 10:24 AM, C. Hatton Humphrey <[email protected]>wrote: > > I ended up moving the EXISTS from the update statement out to an IF... now > it looks something like this: > IF NOT EXISTS(SELECT TOP 1 ColA FROM TableA WHERE PatIndex(''%1IT%'', ColA) > > 0) > UPDATE TableA set ColA = Replace(ColA, ''IAT'', ''1IT'') WHERE > PatIndex(''%IAT%'', ColA) > 0 > > This runs in less than an hour... when I had it as a part of the update > statement it ran for 14+ hours and had not finished yet. > > Trust me, I wish there was a way to do this without a cursor or dynamic SQL > but there are too many instances where VarChar fields in the 1,400+ tables > contain the letter sequence 'IAT' that don't need to be changed to '1IT'. > > > 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. > > > On Wed, May 21, 2014 at 8:24 AM, GMoney <[email protected]> wrote: > > > > > Yeah it's not going to be efficient......'specially with that cursor. > > > > > > On Tue, May 20, 2014 at 3:58 PM, C. Hatton Humphrey <[email protected] > > >wrote: > > > > > > > > Guess it would help if I used the Replace function correctly! > > > > > > Changed it from a replace to a PatIndex('%1IT%', CMLinkID) > 0 > > > > > > Script has been running for 10-ish minutes so something's working > > > correctly. > > > > > > > > > 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. > > > > > > > > > On Tue, May 20, 2014 at 4:34 PM, C. Hatton Humphrey < > [email protected] > > > >wrote: > > > > > > > While syntactically correct, none of the tables updated with the > > > > statements along these lines... > > > > > > > > UPDATE Table2002 set LinkID = Replace(CMLinkID, 'IAT', '1IT') WHERE > NOT > > > > EXISTS (SELECT * FROM Table2002 WHERE LinkID = Replace(CMLinkID, > 'IAT', > > > > '1IT')) > > > > > > > > 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. > > > > > > > > > > > > On Tue, May 20, 2014 at 3:30 PM, GMoney <[email protected]> wrote: > > > > > > > >> > > > >> 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:370322 Subscription: http://www.houseoffusion.com/groups/cf-community/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-community/unsubscribe.cfm
