Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to update data of a particular colum of all the rows in a table at once

 
hitendra sunkara
Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi ,

I have two tables:
1.Cantentry
2.Dmelementnvp

dmelementnvp have this data:
create table dmelementnvp (dmelement_id integer,name varchar(60),value varchar(60))
insert into dmelementnvp values(1,'catlogEntryList',123)
insert into dmelementnvp values(1,'catlogEntryList',124)
insert into dmelementnvp values(1,'catlogEntryList',125)

Catentry has this data:
create table catentry (catentry_id integer,member_id integer,idenifier varchar(60))
insert into catentry values(123,200,'xxx')
insert into catentry values(124,200,'yyy')
insert into catentry values(125,200,'zzz')
insert into catentry values(126,700,'xxx')
insert into catentry values(127,700,'yyy')
insert into catentry values(128,700,'zzz')


Now i need to update value field of dmelementnvp with
126
127
128

instead of
123
124
125

I tried this Query:
update DMELEMENTNVP set value = (select catentry_id from catentry,dmelementnvp where catentry.idenifier
=(select idenifier from catentry where CAST(catentry_id AS VARCHAR(50)) =DMELEMENTNVP.value ) and catentry.member_id = 700 )
where DMELEMENTNVP.name = 'catlogEntryList'

But it is throwing error as : more than one row returned by a subquery used as an expression

So can anyone get me out of this issue..Do i need to write any procedure for updating or..simple uodate Query is enough for this??
 
Surendra Kumar
Ranch Hand
Posts: 236
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Not sure if it was a typo, but the query should be like this (just mismatch of parantheses).
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic