Update query comparing 2 tables? (1 Viewer)

[xytras]

Hi
I have such situation:
i have tables [k] and [r] with street and city field.

I would like set on field[dubel] in the second table [r] in the rows where the street and the city are the same for the both tables.
There could be one to many relations. It means many fields in [k] could have the same as in [r]

I've tried with this query but it marks all the fields....

UPDATE r SET dubel=1 where EXISTS ( SELECT r.str, cit
FROM k, r
WHERE (([k].[str]=[r].[str]) AND ([k].[cit]=[r].[cit])));

when i'm using just select part, it gives me good results.....
Can Anyone help ME?
THANKS

 

[Jon K]

Try this:

UPDATE r INNER JOIN k ON ([r].[cit]=[k].[cit]) AND ([r].[str]=[k].[str]) SET r.dubel = 1;
.