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

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

On 14 Jun, 17:21, Manikandan wrote:
> 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

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

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

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

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