SQL Server - Doubt update and relationship between tables

Question

SQL Server - Doubt update and relationship between tables

Navigation

#1 by (2 votes)
#2 by (0 votes)

4

I have two tables, one call coletor , and another call material , I have a proc called cadMat , which was to register material in the database and by cpf (parameter of proc) put as name of collector the materials that it brought when cpf exists in the database (with an update statement), but when doing this the instruction changes all lines, not some as expected, can anyone help me?

The code is here, if you can.

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

sql sql-server servidor

asked by anonymous 21.05.2015 / 13:18

2 answers

Changing Routes with Asp.Net MVC Graph possible paths python

score 2 · Answer 1

Expected, in fact, is that all the material lines are updated. See your last command:

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

The above command does an UPDATE with no WHERE in the material table; the WHERE is being applied only to the data source table, the collector .

You can fetch the data from the collector table by linking it through JOIN to your UPDATE and inform WHERE which material records to update. So:

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 an "update from with join."

score 0 · Answer 2

Just by complementing Caffé's "correct response", 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:

link