sybase sql

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
FROM ABC
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.

UPDATE ABC
      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,
Bill

use not exists...

UPDATE ABC
      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)
       )

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.

BTW...
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.

Bill

Bill,

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.

Thanks