469,632 Members | 1,732 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,632 developers. It's quick & easy.

update one colum with other column value in same table using update table statement

Hi,
I have table with three columns as below
table name:exp
No(int) name(char) refno(int)

I have data as below
No name refno
1 a
2 b
3 c

I need to update the refno with no values I write a query as below

update exp set refno=(select no from exp)
when i run the query i got error as
Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.

I need to update one colum with other column value.
What is the correct query for this ?

Thanks,
Mani

Jun 14 '07 #1
3 33223
On 14 Jun, 17:21, Manikandan <plmanikan...@gmail.comwrote:
Hi,
I have table with three columns as below
table name:exp
No(int) name(char) refno(int)

I have data as below
No name refno
1 a
2 b
3 c

I need to update the refno with no values I write a query as below

update exp set refno=(select no from exp)
when i run the query i got error as
Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.

I need to update one colum with other column value.
What is the correct query for this ?

Thanks,
Mani
Hi,
I used a query as below

update exp set refno=(select no from exp a where exp.NO =a.NO)

It works fine.
Is it correct?

Thanks,
Mani

Jun 14 '07 #2
If you simply need to update the refno column to the values in the no
column, then you can write your update statement as follows:

UPDATE exp
SET refno = no

Note that this query does not have a WHERE clause and will update all rows.

HTH,

Plamen Ratchev
http://www.SQLStudio.com
Jun 14 '07 #3
Yes, the query is correct, but it does not make sense to use a subquery
here. See my other post for more simplified approach.

Plamen Ratchev
http://www.SQLStudio.com
Jun 14 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Dave | last post: by
8 posts views Thread by Lauren Quantrell | last post: by
17 posts views Thread by kalamos | last post: by
16 posts views Thread by robert | last post: by
8 posts views Thread by Zorpiedoman | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.