Solved

0 rows updated.how to fix

Posted on 2016-10-12
10
46 Views
Last Modified: 2016-11-15
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.
0
Comment
Question by:anil kumar
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 10

Assisted Solution

by:HuaMinChen
HuaMinChen earned 150 total points (awarded by participants)
ID: 41839808
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
 

Author Comment

by:anil kumar
ID: 41839812
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
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 41839818
What is your criteria?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:anil kumar
ID: 41839834
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
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 41839903
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
 
LVL 35

Expert Comment

by:johnsone
ID: 41839904
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
 
LVL 37

Accepted Solution

by:
Geert Gruwez earned 200 total points (awarded by participants)
ID: 41840032
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
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 150 total points (awarded by participants)
ID: 41842350
update premisecountdata
set street_type = decode(street_type,'st','street','rd','road','ave','avenue','dr','drive','cres','crescent','pl','place',street_type);
0
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 41863421
Hi Anil,
Can you please confirm if your original question has been resolved, per the suggestions from all of us?
0
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 41887591
No further update from author.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question