Re: Record by record update with SQL with no matching key.

I was never able to sort out the correct syntax to set the rownumber() over() on the real file = to the rownumber() over() on the "fake" file so that I had a 1 to 1 join condition. I'd love to see some simple syntax that shows how to use column aliases in a "where" or "join" clause (or whatever the equivalent is)

So I fell back to plan B which was to CRTDUPOBJ on the real file, ALTER both tables to add an identity column, join on the identity columns and then issue the following SQL:

update realtable set (first,last,address,city) =
(select F1,F2,F3,F4 from faketable
where realtable.id = faketable.id)
After that I cpyf the contents of the copied and changed "real" table back to the original.

I am sure that there must have been a way to do this without copying and altering the tables, but, finding no workable examples, I went with what I knew.

Pete


Pete Helgren wrote:

So, how would I join the two records by rownumber? I think I have to use a subquery since I'll need to alias the rownumber results but I can't quite get my head around the syntax

The update would look like:
update realtable set firstname = firstfake, lastname = lastfake

the where would use the derived rownumber columns (say rn1,and rn2)

But how to build the suquery so that the records selected could be used for an update is where I get stuck. I tried this:

update realtable set firstreal = firstfake, lastreal = lastfake where rownumber over(order by firstreal,lastreal) = ( select rownumber() over(order by firstfake,lastfake) from fakeemp)
But that didn't work. I need to join on the derived rownumbers. Can't quite sort that out.

Pete


Charles Wilt wrote:

ROWNUMBER()

Charles

On Fri, Jan 29, 2010 at 10:05 PM, Dennis Lovelady <iseries@xxxxxxxxxxxx> wrote:

Probably have to reorg the "live" member so that the rrns were
sequential and in the same range, yes? (the file is quite old and
hasn't
been re-orged ever to my knowledge)

Hmmm... hadn't considered that. Probably; I don't know a way to get
rownumber (as opposed to RRN) from DB2.

Good luck with it; while I haven't done it myself (other than to test the
sample before I sent) it looks workable.

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
Hard work pays off in the future. Laziness pays off now.



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.