asked on

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.

SOLUTION

Peter Chan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anil kumar

ASKER

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

Peter Chan

What is your criteria?

anil kumar

ASKER

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

Geert G

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;

johnsone

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.

ASKER CERTIFIED SOLUTION

Geert G

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

awking00

membership

This solution is only available to members.