SQL Query to copy a field from one row to others
Need help in formulating an SQL Query compatible with DB2 version 5.0
for mainframes, which will copy the value of a column in a row in a
table identified by a where clause, to another set of rows in the same
table identified by another where clause.
To Illustrate:
Table MyTable has columns A, B, C and D
with PK as A.
Want to copy the value of column B in the row which has value for
Column A as "x", into column B for the set of rows which have value
for Column C as "y".
Tried an SQL -
Update MyTable A1
Set A1.B = (Select A2.B from MyTable A2 where A2.A = "x")
where A1.C = "y"
But got an SQL error.
Thanks in Advance
# 1 Re: SQL Query to copy a field from one row to others
SQL looked good, perhaps theres a problem with the assignment of a result
set to a value. Try reading the result returned into a variable and use
the variable inpalce of your your select statement...
Just and Idea, I really can't see why what you have written does not work.
Unless col A isn't really your PK, and the result set is returning 2 rows...
Q*bert
!@#(*
"Vijay Sampath" <shivaji007@yahoo.com> wrote:
>
>Need help in formulating an SQL Query compatible with DB2 version 5.0
>for mainframes, which will copy the value of a column in a row in a
>table identified by a where clause, to another set of rows in the same
>table identified by another where clause.
>To Illustrate:
>
>Table MyTable has columns A, B, C and D
>with PK as A.
>Want to copy the value of column B in the row which has value for
>Column A as "x", into column B for the set of rows which have value
>for Column C as "y".
>
>Tried an SQL -
>
>Update MyTable A1
> Set A1.B = (Select A2.B from MyTable A2 where A2.A = "x")
>where A1.C = "y"
>
>But got an SQL error.
>Thanks in Advance
>
>
Q*bert at 2007-11-11 23:52:51 >
# 2 Re: SQL Query to copy a field from one row to others
Thanks for the response.
But I am not looking for an embedded query where variables could be used.
Need the query to be run using QMF or SPUFI for adhoc database adjustments
for testing purposes. I double checked on the PK bit..it seems ok..logically
the sub- query should return a single value.
