It *should* always be able to be done with sets, but it will also depend on
your db and what it supports. For instance, I've got a problem that needs
to look for gaps in sequences. If I had MS SQL Server 2012, I'd be able to
do it with their windowing functions pretty easily. But I'm on 2008 R2
right now and therefore don't have the proper tools available. They exist
in the ANSI standard and in Oracle but, for whatever reason, MS has been
behind on window functions.
Cheers,
Judah
On Wed, May 21, 2014 at 8:31 AM, GMoney <gm0n3...@gmail.com> wrote:
>
> 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 <chumph...@gmail.com
> >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 <gm0n3...@gmail.com> 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 <
> chumph...@gmail.com
> > > >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 <
> > chumph...@gmail.com
> > > > >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 <gm0n3...@gmail.com>
> 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 <
> > > > chumph...@gmail.com
> > > > >> >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:370328
Subscription: http://www.houseoffusion.com/groups/cf-community/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-community/unsubscribe.cfm
