Update table 3

[TariqMehmod]

Sir while using sqlserver, I have this data

I want to update TIK column as like this

Formula:

If found tik=1 then it pick up weight that is 375

Now 375-25=350

Update table_1 set tik= 1 where weight between 350,375

Please help

 

[bborissov]

Tariq,
please post some example data, that I can copy and paste.
I don't like to write all data by myself when I try to help somebody.


Borislav Borissov
VFP9 SP2, SQL Server

 

[Andrzejek]

If found tik=1 then it pick up weight that is 375" assumes that only one record will be with tik=1. What about if 5 records will have tik=1 ? Which one do you choose as a base for your Update?


---- Andy

There is a great need for a sarcasm font.

 

[TariqMehmod]

Sir only first TIK in the table + id
id of first TIK is 6
table must not updated once it has updated after id=6

 

[TariqMehmod]

Here is sample data

[pre]CREATE CURSOR table_1(id n(2),weight n(4),tik n(6))
INSERT INTO table_1 values(1,400,0)
INSERT INTO table_1 values(2,395,0)
INSERT INTO table_1 values(3,3390,0)
INSERT INTO table_1 values(4,385,0)
INSERT INTO table_1 values(5,380,0)
INSERT INTO table_1 values(6,375,1)
INSERT INTO table_1 values(7,370,0)
INSERT INTO table_1 values(8,365,0)
INSERT INTO table_1 values(9,360,0)
INSERT INTO table_1 values(10,355,0)
INSERT INTO table_1 values(11,350,0)
INSERT INTO table_1 values(12,345,0)
INSERT INTO table_1 values(13,340,0)
INSERT INTO table_1 values(14,335,0)
INSERT INTO table_1 values(15,330,0)
INSERT INTO table_1 values(16,325,0)
INSERT INTO table_1 values(17,320,0)
brow
[/pre]


There will be TIK=1 only one time in the table, so I think no need to run codes again

Regards

 

[Olaf Doschke]

The question is: What is the condition for updating records with Tik=0 to Tik=1 Is it always the 5 records following the first in Id order?

I know you come from VFP, where we have REPLACE NEXT 5, SQL Server has functions like lead and lag about previous next in some order, but that doesn't make it that simple.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Andrzejek]

only first TIK in the table "[tt]
[blue]
SELECT MIN(weight) FROM table_1
WHERE TIK = 1[/blue][/tt]

"Update table_1 set tik= 1 where weight between 350,375"

[pre]
Update table_1
set tik = 1
where weight between
([blue]SELECT MIN(weight) FROM table_1 WHERE TIK = 1[/blue]) AND
([blue]SELECT MIN(weight)[/blue] + 25 [blue]FROM table_1 WHERE TIK = 1[/blue])[/pre]


---- Andy

There is a great need for a sarcasm font.

 

[bborissov]

DECLARE @table_1 TABLE (id int,weight int,tik int)
INSERT INTO @table_1 values(1,400,0)
INSERT INTO @table_1 values(2,395,0)
INSERT INTO @table_1 values(3,3390,0)
INSERT INTO @table_1 values(4,385,0)
INSERT INTO @table_1 values(5,380,0)
INSERT INTO @table_1 values(6,375,1)
INSERT INTO @table_1 values(7,370,0)
INSERT INTO @table_1 values(8,365,0)
INSERT INTO @table_1 values(9,360,0)
INSERT INTO @table_1 values(10,355,0)
INSERT INTO @table_1 values(11,350,0)
INSERT INTO @table_1 values(12,345,0)
INSERT INTO @table_1 values(13,340,0)
INSERT INTO @table_1 values(14,335,0)
INSERT INTO @table_1 values(15,330,0)
INSERT INTO @table_1 values(16,325,0)
INSERT INTO @table_1 values(17,320,0)


UPDATE Tbl1 SET Tik = 1
FROM @table_1 Tbl1 
INNER JOIN @table_1 Tbl2 ON Tbl2.Id= (SELECT MIN(Id) FROM @table_1 WHERE Tik = 1)
WHERE Tbl1.weight BETWEEN Tbl2.weight-25 AND Tbl2.weight

SELECT * FROM @table_1

Borislav Borissov
VFP9 SP2, SQL Server