For / Foreach Sql Server For Each Different Data of a Column

Question

For / Foreach Sql Server For Each Different Data of a Column

Navigation

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

3

I need to execute a SQL command to give an Update to a table, The intention of this update would be to update a column with a sequential number per company, Ex: I have a table Sale in this table I have the column CompanyId for each company I intend to create a sale sequence type Company1 sale 1,2,3 ... Company 2 sale 1,2,3 and so the ID does not respect this order, For Well the problem is in the following, I created the column and need to give an update that does this for the records that already exist, I got with the following code:

DECLARE @NumeroSequencia int
SET @NumeroSequencia = 0
UPDATE Venda
SET @NumeroSequencia = NumeroSequencia = @NumeroSequencia + 1 where 
EmpresaID = 1

But where is the EnterpriseID = 1 I wanted to do something like a foreach in the example but in Sql:

--MONTA UMA LISTA COM OS IDS DE CADA EMPRESA CADASTRADA
declare listaEmpresas = select id from empresa

--PARA CADA EMPRESA EXECUTA O UPDATE COM A SEQUENCIA DAS VENDAS
foreach (var item in listaEmpresas){
DECLARE @NumeroSequencia int
SET @NumeroSequencia = 0
UPDATE Venda
SET @NumeroSequencia = NumeroSequencia = @NumeroSequencia + 1 where 
EmpresaID = item --AQUI SERIA O ID DE CADA EMPRESA SEGUINDO O FOREACH
}

sql sql-server

asked by anonymous 30.12.2017 / 00:57

2 answers

0

One possible solution would be to use the ROW_NUMBER () Partition within a Common_Table_Expression ( CTE) to generate the sales sequential by company and subsequently update the Selling table. The query looks like this:

with Venda_Cte (VendaID, NumeroSequencia)
as
(
  --query cte usando a função ROW_NUMBER com partition para gerar
  --o sequencial da venda para cada empresa
  select VendaID
         ,row_number() over (partition by EmpresaID order by VendaID)
    from Venda
)
--atualizando a tabela Venda com o sequencial da cte
update Venda
   set NumeroSequencia = Venda_Cte.NumeroSequencia  
  from Venda 
  join Venda_Cte on Venda.VendaID = Venda_Cte.VendaID;

30.12.2017 / 15:38

LINQ using function within select new {} with lambda expression How to control migrations two different contexts?

score 3 · Accepted Answer

I propose a solution that does not use loops (at least not explicitly).

We started with setting an example table and loading some records.

CREATE TABLE #Vendas 
(
   VendaID        INT IDENTITY(1, 1),
   EmpresaID      INT,
   ProdutoID      INT,
   Quantidade     INT,
   DataVenda      DATE,
   --Nova coluna com ID de venda por empresa
   EmpresaVendaID INT,
);

--Carregar alguns registos na tabela 
INSERT INTO #Vendas(EmpresaID, ProdutoID, Quantidade, DataVenda)VALUES
(1, 1, 2, '2017-01-01'),
(1, 2, 2, '2017-02-01'),
(1, 2, 3, '2017-03-01'),
(1, 1, 5, '2017-04-01'),
(2, 1, 5, '2017-01-01'),
(2, 1, 4, '2017-02-01');

--SELECT * FROM #Vendas;

Before the UPDATE column, the CompanyDeliveryID shows NULL for all records:

VendaID EmpresaID   ProdutoID   Quantidade  DataVenda    EmpresaVendaID
1       1           1           2           01.01.2017   NULL
2       1           2           2           01.02.2017   NULL
3       1           2           3           01.03.2017   NULL
4       1           1           5           01.04.2017   NULL
5       2           1           5           01.01.2017   NULL
6       2           1           4           01.02.2017   NULL

Now let's update the table. The idea is to generate a sequence for each EnterpriseID, a sequence that always starts at 1. You may need to slightly change the invocation of the ROW_NUMBER function depending on the structure of your table. In this case I assume the existence of a column that is the primary key in the #Sales (VendaID) table.

UPDATE Vd
   SET EmpresaVendaID = Seq.Sequencia
  FROM #Vendas Vd
 INNER JOIN
  (
      SELECT VendaID, 
             ROW_NUMBER() OVER (PARTITION BY EmpresaID ORDER BY VendaID) Sequencia
        FROM #Vendas
  ) Seq
    ON Seq.VendaID = Vd.VendaID
;

More concise alternative

UPDATE Vd
   SET EmpresaVendaID = Sequencia
  FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY EmpresaID ORDER BY VendaID) Sequencia FROM #Vendas ) Vd
;

--SELECT * FROM #Vendas;

After UPDATE this will be the result:

VendaID EmpresaID   ProdutoID   Quantidade  DataVenda    EmpresaVendaID
1       1           1           2           01.01.2017   1
2       1           2           2           01.02.2017   2
3       1           2           3           01.03.2017   3
4       1           1           5           01.04.2017   4
5       2           1           5           01.01.2017   1
6       2           1           4           01.02.2017   2

Stay here the link to the code