Copying data across rows

I have one table where I am trying to copy a number from one field
in one row into another field in another row based on two conditions.
More specifically, I need to copy the number from column DIFF for
group LY into column DIFF_CO for group L+. This is what I would like
to do:
UPDATE mytable
SET diff_co = (SELECT diff FROM mytable WHERE group=LY and name=name1)
WHERE name=name1 and group=L+
... so that my end result looks like this:
GROUP NAME DIFF DIFF_CO
LY Name1 9.9
L+ Name1 10.2 9.9
Where I am running into difficulty is that the select statement returns
242 results, and thus the "Subquery returned more than 1 value" error.
Any suggestions on how I can do this?Hope that following can help you:
1. Verify how many rows are returned by following query:
SELECT distinct diff FROM mytable WHERE group=LY and name=name1
2. If the above query returns only 1 row, then use the following query to
'copy data across rows':
UPDATE mytable
SET diff_co = (SELECT distinct diff FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
"nicole" wrote:
> I have one table where I am trying to copy a number from one field
> in one row into another field in another row based on two conditions.
> More specifically, I need to copy the number from column DIFF for
> group LY into column DIFF_CO for group L+. This is what I would like
> to do:
> UPDATE mytable
> SET diff_co = (SELECT diff FROM mytable WHERE group=LY and name=name1)
> WHERE name=name1 and group=L+
>
> ... so that my end result looks like this:
> GROUP NAME DIFF DIFF_CO
> LY Name1 9.9
> L+ Name1 10.2 9.9
>
> Where I am running into difficulty is that the select statement returns
> 242 results, and thus the "Subquery returned more than 1 value" error.
> Any suggestions on how I can do this?|||> Hope that following can help you:
Thanks for the reply!!
> 1. Verify how many rows are returned by following query:
> SELECT distinct diff FROM mytable WHERE group=LY and name=name1
> 2. If the above query returns only 1 row, then use the following query to
> 'copy data across rows':
Unfortunately, the distinct query returns multiple rows.
Any other suggestions?|||since it gives multiple diff values you have to choose which diff value
you want to use for update
You can do this by either using max, min or top 1 in the subquery.
UPDATE mytable
SET diff_co = (SELECT max(diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
or
UPDATE mytable
SET diff_co = (SELECT min(diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+
or
UPDATE mytable
SET diff_co = (SELECT top 1 (diff) FROM mytable WHERE group=LY and
name=name1)
WHERE name=name1 and group=L+