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