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 moved to PNXXXXX
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 ..