Question about select/update

[scubaman]

Hello,

I need some help

I have a Table

Table>ITEM_LOCACAO
codloc
codprod

And

Produtos
codloc
pontos

I want do a Select in the table ITEM_LOCACAO and
request the ID Codloc, have a lot keys about produtos.

So a example

Id = 9 = Product TAble = 25,26,31

After select, I need, make a update
with a increment + 1 in the table
products.

Ex. Codloc = 25 Points 1 + 1
codloc = 26 points 5 + 1
....

How I can do this

Thanks a lot

Luciano

 

[spazman]

SELECT * from ITEM_LOCACAO WHERE Id = 9 AND [Product Table] in(25,26,31)

Wiull select out of the table but I am confused as to what you are wnating to update is it the same trable just a differnet field?

 

[obislavu]

I understood that you have something like that:

_tITEM_LOCACAO

codloc codprod
---------- ----------
25 9
26 9
31 9
45 10
50 10
37 11


_tPontos
codloc pontos
---------- -----------
25 1
26 5
31 7
50 2
37 4

and you want to increment _tPontos.Pontos for all records for which there is a corresponding codloc in _tITEM_LOCACAO and codprod = 9

This could be done by:

UPDATE _tPontos
SET Pontos = Pontos + 1
FROM dbo._tITEM_LOCACAO INNER JOIN
dbo._tPontos ON dbo._tITEM_LOCACAO.codloc = dbo._tPontos.codloc
AND (dbo._tITEM_LOCACAO.codprod = '9')

The results will be:

codloc pontos
---------- -----------
25 2
26 6
31 8
50 2
37 4

Only the first 3 rows were affected.

-obislavu-