This is because you do not have any criteria about the table being updated.
See, you have:
update reference_vendor set internalid = v.internal_id
reference_vendor is the table you want to update.
Then you have the data source for the update:
from reference_vendor rv
inner join contract_reference v on v.external_id = rv.externalid
Finally, the criteria for which lines you are going to upgrade:
where v.id = '1207847' and v.record_type = 'vendor'
Since you have repeated the reference_vendor table (one for the update and another for the from) and did not include any criteria that limit the rows of the table being updated, the update is applied over the entire table. >
The correct way to do this query is:
update reference_vendor set internalid = v.internal_id
from contract_reference v
where v.external_id = externalid
and v.id = '1207847'
and v.record_type = 'vendor'
The reading is as follows:
update the internalid field of the reference_vendor table by selecting
the records that correspond to the records of contract_reference id =
'1207847' and record_type = 'vendor' using the externalid field as
junction
Only as a comparison, the original query can be read like this:
Refresh the internalid field of the reference_vendor table for all records (since there is no criteria in the where) using the merge of the reference_vendor and contract_reference tables by source field externalid where contract_reference .id = '1207847' and contract_reference.record_type = 'vendor'
