Solved

0 rows updated.how to fix

Posted on 2016-10-12
10
50 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

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

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!

Question has a verified solution.

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

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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 explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

710 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