OraFAQ Forum: SQL & PL/SQL » unable to update table

Home » SQL & PL/SQL » SQL & PL/SQL » unable to update table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

unable to update table [message #200713]

Wed, 01 November 2006 01:06

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

Report message to a moderator

Re: unable to update table [message #200717 is a reply to message #200713]

Wed, 01 November 2006 01:21

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';

Report message to a moderator

Re: unable to update table [message #200731 is a reply to message #200713]

Wed, 01 November 2006 02:03

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.

Report message to a moderator

Re: unable to update table [message #200735 is a reply to message #200713]

Wed, 01 November 2006 02:12

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

Report message to a moderator

Re: unable to update table [message #200739 is a reply to message #200735]

Wed, 01 November 2006 02:32

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

Report message to a moderator

Re: unable to update table [message #200745 is a reply to message #200739]

Wed, 01 November 2006 02:48

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: unable to update table [message #200796 is a reply to message #200713]

Wed, 01 November 2006 06:34

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

Report message to a moderator

Re: unable to update table [message #200804 is a reply to message #200796]

Wed, 01 November 2006 07:11

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'

Report message to a moderator

Re: unable to update table [message #200814 is a reply to message #200713]

Wed, 01 November 2006 07:46

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

Report message to a moderator

Re: unable to update table [message #200818 is a reply to message #200814]

Wed, 01 November 2006 07:59

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

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

Report message to a moderator

Re: unable to update table [message #200819 is a reply to message #200818]

Wed, 01 November 2006 08:05

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

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

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.

Report message to a moderator

Re: unable to update table [message #200822 is a reply to message #200819]

Wed, 01 November 2006 08:15

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Once again, because there are trailing spaces or other control characters in your column.

Report message to a moderator

Previous Topic:	how I can delete a row
Next Topic:	sql plus question

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Dec 06 16:47:05 CST 2024