Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

0 rows updated.how to fix

Posted on 2016-10-12
10
Medium Priority
?
57 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 11

Assisted Solution

by:HuaMinChen
HuaMinChen earned 600 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 11

Expert Comment

by:HuaMinChen
ID: 41839818
What is your criteria?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 38

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 38

Accepted Solution

by:
Geert Gruwez earned 800 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 600 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 11

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 11

Expert Comment

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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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 video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

705 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