permaculture playing cards*
The moose likes JDBC and the fly likes How to update data of a particular colum of all the rows in a table at once Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to update data of a particular colum of all the rows in a table at once" Watch "How to update data of a particular colum of all the rows in a table at once" New topic
Author

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

hitendra sunkara
Ranch Hand

Joined: Jul 25, 2013
Posts: 34
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

Joined: Jul 04, 2006
Posts: 235
Not sure if it was a typo, but the query should be like this (just mismatch of parantheses).
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: How to update data of a particular colum of all the rows in a table at once
 
Similar Threads
Which type of query should be use??
Which type of query should be use??
Problem with SQL uqery or DSN
Displaying Sql query string results in jsp by using Spring Framework
Not able to start the Hibernate DB server.