SQL Server - Doubt update and relation between tables

Question

I have two tables, one call coletor, and another call material, have a proc calling for cadMat, that was to register material in the bank and by Cpf (parameter of proc) put as the name of the collector the materials that he brought when Cpf exists in the bank (with an instruction update), but in doing so the instruction changes all lines, and not some as expected, someone can help me?

The code is here if it helps.

create proc cadMat /*Cadastra o material no banco de dados Ok!(sistema)*/
@cpfColetor char(12),
@nomeMaterial varchar(30),
@tipoMaterial varchar(8),
@zonaMaterial varchar(10),
@qtdeMaterial float,
@dataRecebimentoMaterial datetime
as
begin
    insert into material values(@nomeMaterial,@tipoMaterial,@zonaMaterial,@qtdeMaterial,@dataRecebimentoMaterial,@cpfColetor,'')
    update coletor 
    set qtdeMatCol +=@qtdeMaterial where cpfCol = @cpfColetor
    update material set nomeColMat = nomeCol from coletor where cpfCol = @cpfColetor
end

Answer 1

The expected, in fact, is yes that all lines of material are updated. See your last command:

update material set nomeColMat = nomeCol from coletor where cpfCol = @cpfColetor

The above command makes an UPDATE without any WHERE in the table material; the WHERE is being applied only in the data source table, collector.

You can search the table data collector linking it via JOIN to your UPDATE and informing WHERE which records material should be updated. Thus:

update material 
set material.nomeColMat = coletor.nomeCol 
from material
join coletor on coletor.cpfCol = material.cpfCol
where material.cpfCol = @cpfColetor

We can say that what we used above was a "update from with Join".

Answer 2

Just by complementing "the correct answer" of Caffé, your UPDATE is actually performing a "CROSS JOIN" with the "material" table because it is not defining the records that should be affected in its update.

For more information see:

https://msdn.microsoft.com/pt-br/library/ms177523.aspx