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

update realtable set (first,last,address,city) = (select F1,F2,F3,F4
from faketable, crosstable
where crosstable.fakeid = faketable.id and crosstable.realid =
realtable.realid)

crosstable columns has two columns: realid, fakeid

I think you could do this with rrn() as well.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Pete Helgren
Sent: Tuesday, February 02, 2010 10:52 AM
To: Midrange Systems Technical Discussion
Subject: 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.

--
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.