0 rows updated.how to fix

iam trying to update my table.it is not updating even my procedure runs successfully.i ran the update statement on single column though its not updating.can anybody help me.
anil kumarAsked:
Who is Participating?
 
Geert GConnect With a Mentor Oracle dbaCommented:
my previous query would update all rows
this will only update the rows with your specific criteria

update premisecountdata 
set street_type=  
  case street_type 
    when 'st' THEN 'street'
    when 'rd' THEN 'road'
    when 'ave' then 'avenue'
    when 'dr' then 'drive'
    when 'cres' then 'crescent'
    when 'pl' then 'place'
 end
where street_type in ('st', 'rd', 'ave', 'dr', 'cres', 'pl');

Open in new window

0
 
HuaMin ChenConnect With a Mentor System AnalystCommented:
Hi,
Check WHERE conditions/criteria to see if there're relevant records touched.

Or use SELECT instead by putting the same WHERE conditions/criteria, to see if there're records returned.
0
 
anil kumarAuthor Commented:
i have checked it already.it works on  all sample tables.but it is not working on my required table which consists of 2000 rows
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
HuaMin ChenSystem AnalystCommented:
What is your criteria?
0
 
anil kumarAuthor Commented:
my table having street type column consists of columns like st,rd,pl,ave etc.now i have to update the columns like st as 'street' and rd as 'road 'and pl as 'place'.this is my  criteria.iam sending the file so kindly go through once
New-Text-Document.txt
0
 
Geert GOracle dbaCommented:
why pl/sql ?

update premisecountdata
set street_type=  
  case street_type
    when 'st' THEN 'street'
    when 'rd' THEN 'road'
    when 'ave' then 'avenue'
    when 'dr' then 'drive'
    when 'cres' then 'crescent'
    when 'pl' then 'place'
 else street_type end;
0
 
johnsoneSenior Oracle DBACommented:
Why the PL/SQL block?  It really isn't doing anything for you that just running the update statements wouldn't do.
update premisecountdata set street_type='street' where street_type='st';
update premisecountdata set street_type='road' where street_type='rd';
update premisecountdata set street_type='avenue' where street_type='ave';
update premisecountdata set street_type='drive' where street_type='dr';
update premisecountdata set street_type='crescent' where street_type='cres';
update premisecountdata set street_type='place' where street_type='pl';
commit;

Open in new window

Essentially that is what you are doing in the PL/SQL block anyway, you are just using a lot of code to get there.
0
 
awking00Connect With a Mentor Commented:
update premisecountdata
set street_type = decode(street_type,'st','street','rd','road','ave','avenue','dr','drive','cres','crescent','pl','place',street_type);
0
 
HuaMin ChenSystem AnalystCommented:
Hi Anil,
Can you please confirm if your original question has been resolved, per the suggestions from all of us?
0
 
HuaMin ChenSystem AnalystCommented:
No further update from author.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.