I have two tables that have some related data in them. Unfortunately
there's no ID field that I can use to link these tables together when
retrieving data. Both tables do have a unique ID field, but it's not
the same in each. So, let's call these table A and table B. A and B
have about five separate fields that can be used to link them together,
and it's a bit slow. I'd like to add an ID field that they will have in
common and then I'd like to populate this field. Here's what I'd like
to do:

-- The A_ID field will hold the ID from the A table so I can join these
easily.
alter table B add A_ID integer;

update B set A_ID =
select first 1 A.ID
from A
left join B on B.A_ID = A.ID
where B.ID is null and -- Make sure this is only used once!
B.Field1 = A.Field1 and
B.Field2 = A.Field2 and
B.Field3 = A.Field3 and
B.Field4 = A.Field4 and
B.Field5 = A.Field5;

Does this make sense? I want to retrieve the first ID from the A table
where the 5 fields match. I realize that this could lead to a duplicate
though, so I only want to retrieve the A.ID once only. There may be
multiple records where the 5 fields match. Because of this I'm joining
back to the B table on that ID to see if I've already used that ID. If
I have, this should find the second instance of these 5 fields matching
and so on.

My question is: Does this update statement see the previous updates it
has done as it goes through the B table? Has the update for the first
row 'committed' for this update statement's transaction to see when it
starts the update on the second row? If not, then this will not work
for me. Any ideas?


[Non-text portions of this message have been removed]