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

I think it will not update when the subselect is null. If needed, use
where exists on the same subselect.

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)
where exists (select 1 from faketable, crosstable where
crosstable.fakeid = faketable.id and
crosstable.realid = realtable.realid)

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dennis Lovelady
Sent: Tuesday, February 02, 2010 1:22 PM
To: 'Midrange Systems Technical Discussion'
Subject: 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.

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.