sybase sql

Posted on 2007-07-26
Last Modified: 2008-01-09
experts -
I asked this question before and got an excellent answer:

working like a charm.

But have another issue which I didnt think of that at that time.
what if there is already a row with PNXXXX in the table -- then it will have a duplicate row.

Basically it should leave that row untouched
so this is what needs to be done

Table (ABC) has 5 cols

the table has to update all the rows where block_acc or sub_acc like 'V1%' to 'PNXXXX'
but don't touch the rows that are already there with PNXXXX, and I have to prepare a report also something like:

V1XXXX not touched because PNXXXX already exist.

earlier I thought solution will work as below:

      SET block_acc = STUFF(block_acc,1,2,'PN')
 WHERE block_acc LIKE 'V1%'


      SET sub_acc = STUFF(sub_acc,1,2,'PN')
 WHERE sub_acc LIKE 'V1%'

but I missed the point if PN rows are already there.

So I guess below should be done:
1). select into temp all the rows which starts with V1
2). Join ABC table with temp table and somehow get only the rows which are not there.

well, not sure how to do this and to have data for reporting purpose also ..
please help!
Question by:irodov
    LVL 19

    Expert Comment

    You are one the right track with the temp table.  I would probably do this in several steps:
    1) Create the temp table with ALL the candidate rows and the new values
    2) Figure out which temp table rows would cause a collision and mark them as such
    3) Perform the update to the rows that will not cause a collision
    4) Query the temp table to create the "report" of what work was done and what collisions were avoided

    Do something like...

    SELECT block_acc, sub_acc,
                 CASE WHEN block_acc LIKE 'V1%'
                          THEN STUFF(block_acc,1,2,'PN')
                          ELSE null END AS new_bacc,
                 CASE WHEN sub_acc LIKE 'V1%'
                          THEN STUFF(sub_acc,1,2,'PN')
                          ELSE null END AS new_sacc,
                 'N' as CollisionFlag
       INTO #tbl
    WHERE block_acc LIKE 'V1%' or sub_acc LIKE 'V1%'

    Now you have #tbl that has all of the candidate work to be done.  Next, join it to the production table and mark the rows that cannot be updated because there is already a row with the new value.  This might be a bit slow if you do not have indexes on sub_acc or block_acc, especially if you have a large amount of data.

    UPDATE #tbl
        SET CollisionFlag = 'Y'
      FROM #tbl
      JOIN ABC
         ON ABC.block_acc = #tbl.new_bacc
         OR ABC.sub_acc = #tbl.new_sacc

    This marks the rows that you cannot use to update the production data.  Now update the production data.

          SET block_acc = CASE WHEN T.new_bacc != T.block_acc
                                              THEN T.new_bacc ELSE block_acc END,
                 sub_acc = CASE WHEN T.new_sacc != T.sub_acc
                                             THEN T.new_sacc ELSE sub_acc END
       FROM ABC A
        JOIN #tbl T
           ON A.block_acc = T.block_acc
         AND A.sub_acc = T.sub_acc
    WHERE T.CollisionFlag = 'N'

    Now you can create whatever reports you need with simple queries of the temp table since all of the information is there.

    Best of luck,
    LVL 50

    Expert Comment

    use not exists...

          SET sub_acc = STUFF(sub_acc,1,2,'PN')
     WHERE sub_acc LIKE 'V1%'
      and not exists (select sub_acc from abc as x where x.sub_acc = 'PN' + substring(abc.sub_acc ,3,length(abc.sub_acc)-3)
    LVL 19

    Expert Comment

    Not exists works but is generally not considered the preferred syntax because it can have significant performance problems.

    In this case, you still have the problem of writing the report(s) on what happened which may require you to pay for the NOT EXISTS overhead twice.  In this case, it is worse than that because if you do the update directly, you loose the information about what you did change.

    If you need NOT EXISTS functionality, it is usually best to do a LEFT OUTER JOIN and check for a NULL key on the right hand table.


    Author Comment


    Your solutions works like a charm, just have few questions and I will close this,

    1). Usually we join table as below:
    Select * from ABC, #tbl............

    Not sure what this means:

    UPDATE #tbl
        SET CollisionFlag = 'Y'
      FROM #tbl
      JOIN ABC
         ON ABC.block_acc = #tbl.new_bacc
         OR ABC.sub_acc = #tbl.new_sacc

    I mean JOIN ABC   ---   what is the meaning of it and what type of join is it...
    is it equivalent of saying
    Select * from ABC, #tbl............

    where do we use JOIN instead of the way I am joining?

    2). In my case -- servers replicate to different hubs .. do you see issues with any of above which can cause replication to break.

    3). select into in my case is disabled -- so I will create the table and insert into it..  but this should be okay.

    LVL 19

    Accepted Solution

    First, SELECT/INTO BULK COPY should NEVER, NEVER be disabled on the TEMPDB.  It is enabled by default and no one (including the DBA) should ever disable it on TEMPDB.  This may be having a bad effect on performance for queries that create work tables under the covers.  In any case, there is no danger of corrupting the tempdb in the event of a mid-transaction system crash since tempdb is recreated each time the database is started.

    It may well be disabled on your user database (which will not prevent select/into a #table operation).  Since you are doing replication, presumably with Sybase RepServer, the DBA may have felt that this would prevent any unlogged operations that could screw up the replication scheme.

    Bottom line, if your DBA has disabled this in TEMPDB, get him to change it, or have him contact me and I will 'splain it to him/her.

    Now, about your syntax question.....

    The word JOIN is part of the ANSI SQL standard Join syntax and is documented in the Sybase manuals.  (You have downloaded the PDFs to your PC for quick reference, right?).

    The Join syntax is functionally equivalent to the FROM tab1, tab2, ... WHERE sarg, sarg, sarg... syntax EXCEPT when you are doing Left or Right Outer joins.  Outer joins are less ambiguously expressed using the ANSI syntax.

    The reason I used the ANSI syntax here is that is often easier to read and the intent (Join sargs versus filter sargs) is clearer to the reader.  I have slowly converted to the ANSI syntax for everything simply so the next poor schmuck who comes along will have an easier time maintaining it.

    Replication will not be broken.  Sybase RepServer reads the log for most things (don't ask about the exceptions) and propagates the changes that way.  

    Best of luck,

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Outlook Free & Paid Tools
    If you're not part of the solution, you're part of the problem.   Tips on how to secure IoT devices, even the dumbest ones, so they can't be used as part of a DDoS botnet.  Use PRTG Network Monitor as one of the building blocks, to detect unusual…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    This video discusses moving either the default database or any database to a new volume.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now