Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


sybase sql

Posted on 2007-07-26
Medium Priority
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
  • 3
LVL 19

Expert Comment

ID: 19578379
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.

    SET CollisionFlag = 'Y'
  FROM #tbl
     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
    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

ID: 19584406
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

ID: 19586673
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

ID: 19611811

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:

    SET CollisionFlag = 'Y'
  FROM #tbl
     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

grant300 earned 500 total points
ID: 19612370
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,

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Machine Learning is one of the profound applications of AI and therefore, just like AI, it is surrounded by myths and fears. Check out these facts about ML that demystify the related myths.
Use this step by step method when setting up QuickBooks Online. They will allow you to explore the various features of the advanced settings available to you.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses
Course of the Month15 days, 9 hours left to enroll

580 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