Is it possible to update the first 100 lines in sql(server)?

Asked

Viewed 1,923 times

3

UPDATE tbl_venda
SET produto=1
WHERE produto=3

The problem is that there are more than 10,000 sales lines and the pc hangs. I wish it was possible to do 100 to 100, 500 to 500, so it doesn’t get heavy.

sql sql-server

  • What is the structure of the tbl_sale table? There is a column indicating the date and time of sale, for example?

  • what you need is to update the 3 products to 1 code or the SQL code you transcribed is just a template?

5 answers

4

That’s possible, just put one TOP(100) after update, e.g.:

UPDATE top(100) tbl_venda
SET produto=1
WHERE produto=3

3

If what you need is only to change product code 3 to 1 in table rows tbl_sell, as it is in the code you transcribed for this topic, then the solution is simple.

-- código #1
declare @Continua int, @tamBloco int;
set @tamBloco= 500;
set @Continua= 1;

while (@Continua > 0)
  begin
  --
  UPDATE top (@tamBloco) tbl_venda
    set produto= 1
    where produto = 3;
  -- 
  set @Continua= @@rowcount;
  -- pausa (10 segundos entre execuções: "pra não ficar pesado")
  waitfor delay '0:00:10';
  end;

In the above code, the @tamBloco variable informs how many lines the update will occur; adjust to the desired value.

The UPDATE statement is executed several times, until there are no more lines to update.

2

2

Use the reserved word top, as follows:

UPDATE top (100) tbl_venda
SET produto=1
WHERE produto=3

2

Clause-less ORDER BY the definition of TOP doesn’t make much sense. You need an order to know what is 'up' and 'down'

Although the accepted server is not guaranteed which records will be affected.

Something like that is more consistent.

;WITH CTE AS 
( 
SELECT TOP 100 * 
FROM TBL_VENDA WHERE PRODUTO=3 AND ID >= PROXIMO_ID
ORDER BY ID
) 
UPDATE CTE SET PRODUTO=1

The next variable_id starts with 0 in the first UPDATE and should assume other values as the updates are being made, because otherwise you can not give updates in the next records.

  • In the specific case of this topic, it seems to me that ORDER BY is not necessary because what is being changed is the product code (from 3 to 1) sold. At each UPDATE execution will always come lines that have not been updated.

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.