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:370311
Subscription: http://www.houseoffusion.com/groups/cf-community/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-community/unsubscribe.cfm
