Decode limited expression?

Advertisement

Decode limited expression?
Hello Guru's,
I have a table t with 13,000 records.
I am attempting to update records in a column, example:
update t
set col1=(decode(col1,'AA','Antiseptic',
,'BB,'Builder',
,'CC','Contractor'))
commit;
The problem is that I have to update around 300 types of AA, BB at once- needless to say DECODE has it's limits. So, not being a very good developer I decided to break the decode updates to every 40 or so types- this did not work.
It would update only on the first set of decoding I did- around 40 enteries, then the next update I made did not work.
Is there anyway I can update these all 300 types in one execution? Or is there another way to update the records with another expression other than decode that will allow 300 plus type updates?
I hope this is not confusing.
Repaly
While my strong preference would be to use a dummy table approach as Eric and 519668 suggested, if y ou really want to go the DECODE route, and I still prefer the dummy table, then your update statements need to be like:
UPDATE t
SET col = DECODE(col, 'AA', 'Antiseptic',
                      'BB', 'Builder',
                      'CC', 'Contractor')
WHERE col in ('AA', 'BB','CC');
UPDATE t
SET col = DECODE(col, 'DD', 'Developer',
                      'EE', 'Engineer',
                      'FF', 'Foo')
WHERE col in ('DD', 'EE','FF');But really, put the values in a table and use that table to update as suggested.
HTH
John
Read More: The other 7 answers