Solved

0 rows updated.how to fix

Posted on 2016-10-12
10
40 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 34

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

773 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