Home » SQL & PL/SQL » SQL & PL/SQL » unable to update table
unable to update table [message #200713] Wed, 01 November 2006 01:06 Go to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
hi all

i have updated several rows on a table but i fail to update one row. i m in a puzzle now.by that method i update the other rows but why '0 row update' message i got on that particular row.

my table & queries r as follw--

ashish>update BOOKSHELF set categoryname='adultfree', rating=2
2 where AUTHORNAME='soren kierkegaard';

0 rows updated.

TITLE AUTHORNAME CATEGORYNAME RATING
------------------------------ ------------------------- ------------ ----------
to kill a mocking bird harper lee adultfic 5
wonderfull life stephen jay gould adultnf 5
innumeracy john allen paulos adultnf 4
kierkegaard anthology robert bretall adultref 3
kierkegaard anthology soren kierkegaard
anne of green gables lucy maud montgomery childrenpic 1
good dog, carl alexandra day
letters and papers from prison dietrich bonhoeffer

8 rows selected.

ashish>update BOOKSHELF set categoryname='childrenfic', rating=3
2 where AUTHORNAME ='alexandra day';

1 row updated.

ashish>update BOOKSHELF set categoryname='adultnf', rating=4
2 where AUTHORNAME='dietrich bonhoeffer';

1 row updated.



with regards
ashish
Re: unable to update table [message #200717 is a reply to message #200713] Wed, 01 November 2006 01:21 Go to previous messageGo to next message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member

you may have spaces along with authorname try with trimming the spaces

Select it first before update like below

Select * from BOOKSHELF where ltrim(rtrim(AUTHORNAME)) = 'soren kierkegaard';
Re: unable to update table [message #200731 is a reply to message #200713] Wed, 01 November 2006 02:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Looking at the data you presented
TITLE                          AUTHORNAME                CATEGORYNAME RATING
------------------------------ ------------------------- ------------ ----------
kierkegaard                    anthology                 soren kierkegaard
You seem to have the authorname and categoryname the wrong way round.
Re: unable to update table [message #200735 is a reply to message #200713] Wed, 01 November 2006 02:12 Go to previous messageGo to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
hello JRowbottom

i have done mistake while posting, sorry for it.
again i will post it.

with regards
ashish
Re: unable to update table [message #200739 is a reply to message #200735] Wed, 01 November 2006 02:32 Go to previous messageGo to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
hello JRowbottom

my table is like this---(which was as earlier)


SQL> select * from bookshelf;

TITLE AUTHORNAME CATEGORYNAME RATING
------------------------------ ------------------------- ------------ ----------
to kill a mocking bird harper lee adultfic 5
wonderfull life stephen jay gould adultnf 5
innumeracy john allen paulos adultnf 4
kierkegaard anthology robert bretall adultref 3
kierkegaard anthology soren kierkegaard
anne of green gables lucy maud montgomery childrenpic 1
good dog, carl alexandra day childrenfic 3
letters and papers from prison dietrich bonhoeffer adultnf 4

8 rows selected

kierkegaard anthology comes under titlesoren kierkegaard comes under authorname

now is there any doubt?

please provide me help.

with regards
ashish

Re: unable to update table [message #200745 is a reply to message #200739] Wed, 01 November 2006 02:48 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Ashish, you're on this forum for several months now; I believe you've seen people using [code] tags. Those are here for reason - to improve output visibility.
Ashish
kierkegaard anthology comes under titlesoren kierkegaard comes under authorname

As far as I can tell, nothing comes under anything in your post.

Here is a link to the BB Code page - scroll through it and see which tag does what. Then post your data again, but in the way we could read (and understand) it.
Re: unable to update table [message #200796 is a reply to message #200713] Wed, 01 November 2006 06:34 Go to previous messageGo to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
hello littlefoot

thanxx
for giving the link..i want to know about it but i m not getting the right forum where i could ask for it.

i have updated several rows on a table but i fail to update one row

SQL> select * from bookshelf;

TITLE                          AUTHORNAME                CATEGORYNAME     RATING
------------------------------ ------------------------- ------------ ----------
to kill a mocking bird         harper lee                adultfic              5
wonderfull life                stephen jay gould         adultnf               5
innumeracy                     john allen paulos         adultnf               4
kierkegaard anthology          robert bretall            adultref              3
kierkegaard anthology          soren kierkegaard
anne of green gables           lucy maud montgomery      childrenpic           1
good dog, carl                 alexandra day             childrenfic           3
letters and papers from prison dietrich bonhoeffer       adultnf               4

8 rows selected.



with regards
ashish

Re: unable to update table [message #200804 is a reply to message #200796] Wed, 01 November 2006 07:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What does this query return:

SELECT * from bookshelf where authorname = 'soren kierkegaard'
Re: unable to update table [message #200814 is a reply to message #200713] Wed, 01 November 2006 07:46 Go to previous messageGo to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
hello JRowbottom

this is my result what i get-

SQL> select * from bookshelf 
  2  ;

TITLE                          AUTHORNAME                CATEGORYNAME     RATING
------------------------------ ------------------------- ------------ ----------
to kill a mocking bird         harper lee                adultfic              5
wonderfull life                stephen jay gould         adultnf               5
innumeracy                     john allen paulos         adultnf               4
kierkegaard anthology          robert bretall            adultref              3
kierkegaard anthology          soren kierkegaard
anne of green gables           lucy maud montgomery      childrenpic           1
good dog, carl                 alexandra day             childrenfic           3
letters and papers from prison dietrich bonhoeffer       adultnf               4

8 rows selected.

SQL> select * from bookshelf 
  2  where authorname='soren kierkegaard';

no rows selected

SQL>  select * from bookshelf
  2  where authorname='harper lee';

TITLE                          AUTHORNAME                CATEGORYNAME     RATING
------------------------------ ------------------------- ------------ ----------
to kill a mocking bird         harper lee                adultfic              5



regards
ashish
Re: unable to update table [message #200818 is a reply to message #200814] Wed, 01 November 2006 07:59 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
So, it clearly shows that the problem is with the data.

Try this

select '**' || AUTHORNAME || '**' aa,
length(AUTHORNAME) bb
  from bookshelf 
 where title = 'kierkegaard anthology'
   and CATEGORYNAME is null


If at least the title is correct, then you can update the authorname first and the rest next.

By
Vamsi
Re: unable to update table [message #200819 is a reply to message #200818] Wed, 01 November 2006 08:05 Go to previous messageGo to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
hello vamsi
thanx for your kindness.
but can u please tell me why other's row updated, only single row unable to update.i will try ur method.

thanx
ashish

[Updated on: Wed, 01 November 2006 08:05]

Report message to a moderator

Re: unable to update table [message #200820 is a reply to message #200819] Wed, 01 November 2006 08:11 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Hi Ashish,
I think some how the data has been entered wrongly for that particular row.

The authorname is not equal to 'soren kierkegaard'. That we are sure according the result of the following query. Some special character[s] might be there in the middle of the string or so.

select * from bookshelf 
 where authorname='soren kierkegaard';


If you get the result of my previous post, you only can figure the problem out.

By
Vamsi.
Re: unable to update table [message #200822 is a reply to message #200819] Wed, 01 November 2006 08:15 Go to previous message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Once again, because there are trailing spaces or other control characters in your column.
Previous Topic: how I can delete a row
Next Topic: sql plus question
Goto Forum:
  


Current Time: Thu Dec 08 04:13:21 CST 2016

Total time taken to generate the page: 0.05874 seconds