Problem in a simple Inner Join Update

Question

I’m running a simple update on my base, however set which I am giving, is being replicated for all rows of the table.

Follow the script executed:

update reference_vendor set internalid = v.internal_id 
from reference_vendor rv
inner join contract_reference v on v.external_id = rv.externalid
where v.id = '1207847'
and v.record_type = 'vendor'

In the above case I pass in the Where clause the id I want to give the update, however it is replicating for all lines.

Follow the print:

What am I doing wrong?

Follows structure of the table:

reference_vendor:

contract_reference:

Answer 1

This is because you have no criteria about the table being updated.

See, you got:

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 criterion for which lines you will update:

where v.id = '1207847' and v.record_type = 'vendor'

As you repeated the table reference_vendor (one for the update and one for the from) and did not include any criteria that limit the rows of the table being updated, the update is applied over the whole 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 records that match contract_reference id records = '1207847' and record_type = 'vendor' using the externalid field as junction

Only as a comparison, the original query can be read like this:

update the internalid field of the reference_vendor table for all records (since there is no criterion in Where) using as source the junction of the reference_vendor and contract_reference tables by the externalid field where contract_reference . id ='1207847' and contract_reference.record_type = 'vendor'