Did you try it like this?
select last, F2,
rrn(faketable) as rownum1, rrn(realtable) as rownum2 from
faketable,realtable
where rrn(faketable) = rrn(realtable)
The aliases on functions don't bubble up until after the select.
If you have to, you could probably do this:
select last, F2, rownum1, rownum2 from
((select last, F2, rrn(faketable) as rownum1 from faketable),(select
rrn(realtable) as rownum2 from realtable)
where rownum1 = rownum2 )
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Pete Helgren
Sent: Tuesday, February 02, 2010 2:16 PM
To: Midrange Systems Technical Discussion
Subject: Re: Record by record update with SQL with no matching key.
Well, the kicker for me was no common key to join them on in the first
place hence the initial foray into using rrn or the rownumber options.
Looks like Dan's solution still requires that the tables have a common
key to join on, which mine originally didn't have.
However, I can't do a "join" or a "where" on rrn or rownumber, or even
an alias of them (at least I never figured out HOW to do it).
Tried stuff like (just selecting to start with):
select last, F2,
rrn(faketable) as rownum1, rrn(realtable) as rownum2 from
faketable,realtable
where rownum1 = rownum2
Also tried it with rownumber() over(). But every time got the error:
Column ROWNUM1 not in specified tables. This presumably because you
can't use "where" or "join" with an alias. So it was just expediant to
create a "real" id by adding an identity column to each table and then
joining them for the update.
It worked. Which was all I was after. But I am sure it could have been
done "better"
Pete
Dennis Lovelady wrote:
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.
Interesting. But don't you need a WHERE clause of some sort on the
UPDATE also (not just on the subselect)?
Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"Always live within your income, even if you have to borrow money to
do so."
-- Josh Billings
--
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.
midrange.com
