SQL

[cjany]

I need an SQL to update checknumbers where the 1st digit was incorrectly coded. There is a range of check numbers all updated on the same date (20061010), but the 1st digit on all of these numbers was applied with a 7 instead of a nine. Ex. 75785 should have been 95785. Here is the SQL that I've been playing with but I'm not sure this is programmatically possible:

UPDATE JANYSEC/CAUSENUMBE SET EI2CASNUM =(SELECT TRIM(LEADING ' ' FROM ei2casnum)from janysec/causenumbe)

After this would be applied I would use the same concept with a concat to add the 9 to the start of the number.

I've also considered using Select Replace.

Any suggestions anyone???

 

[rstitzel]

update janysec/causenumbe set ei2casnum = ei2casnum + 20000 where ei2casnum <= 70000 and ei2casnum <= 79999 and yourdatefieldname = 20061010

 

[rstitzel]

Correction:

update janysec/causenumbe set ei2casnum = ei2casnum + 20000 where ei2casnum >= 70000 and ei2casnum <= 79999 and yourdatefieldname = 20061010