Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 for loop SQL to update a table by variable

Author  Topic 

jimmy2090
Starting Member

26 Posts

Posted - 2013-07-22 : 09:38:53
dear all,

i facing a problem to write a for loop SQL to update a variable to a table.

below is my code:

declare @root_ID int
declare @CHILD_PART_NO Varchar (10)

SELECT @CHILD_PART_NO = CHILD_PART_NO, @root_ID = ROWID FROM IPL
WHERE SUBSTRING (CHILD_PART_NO , 1,1) = 'F'


>> i will get some variable 'child_part_no' and 'root_ID' from this table, then from this child_part_no i need to update it to F_NO as below:

UPDATE IPL SET f_NO = @CHILD_PART_NO where ROWID > = @root_ID


please help, i had spent a couple of days, but still cannot solve it.
i almost want to kill myself.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-22 : 09:50:02
quote:
Originally posted by jimmy2090

dear all,

i facing a problem to write a for loop SQL to update a variable to a table.

below is my code:

declare @root_ID int
declare @CHILD_PART_NO Varchar (10)

SELECT @CHILD_PART_NO = CHILD_PART_NO, @root_ID = ROWID FROM IPL
WHERE SUBSTRING (CHILD_PART_NO , 1,1) = 'F'


>> i will get some variable 'child_part_no' and 'root_ID' from this table, then from this child_part_no i need to update it to F_NO as below:

UPDATE IPL SET f_NO = @CHILD_PART_NO where ROWID > = @root_ID


please help, i had spent a couple of days, but still cannot solve it.
i almost want to kill myself.

Not quite clear to me the logic you are trying to implement - but from what I understand you are:

1. Getting a child part number and root id from the IPL table. You are expecting exactly one row to be returned from this query.

2. Update all the f_no values in table IPL with the child part number from step 1 for all rows for which rowid is greater than or equal to the row id that you got in step 1.

When you say you can't get the loop to run, what kind of loop are you trying to run? Are you trying to do this for a number of root ids and child part numbers?

Post some representative sample data in a consumable format - that would make it easier for everyone to understand the problem you are trying to solve and propose good solutions.

This blog might help you in formulating the question: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

jimmy2090
Starting Member

26 Posts

Posted - 2013-07-22 : 10:02:04
yes, here is my answer for your question:

1. Getting a child part number and root id from the IPL table. You are expecting exactly one row to be returned from this query.
i expect more than 1 row of data will return from this query.

Example i get 2 row of data from this query:
1st Row: F1000555 (Child_Part_No) ; 100444 (Root_ID)
2nd Row: F2334444 (Child_Part_No) ; 100999 (Root_ID)

then i need to update F1000555 to column (F_No) where the ROWID bigger than the root_ID 100444
then next
update F2334444 to column (F_No) where the ROWID bigger than the root_ID 100999
(i am facing problem to write this part of for loop)
Go to Top of Page

jimmy2090
Starting Member

26 Posts

Posted - 2013-07-22 : 11:37:42
any idea? please help...
thank you in advance...
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-22 : 11:50:07
Something like this?
UPDATE		w
SET w.f_NO = w.Child_Part_No
FROM dbo.IPL AS w
INNER JOIN (
SELECT Child_Part_No,
MIN(RowID) AS theMin,
MAX(RowID) AS theMax
FROM dbo.IPL
) AS q ON q.Child_Part_No = w.Child_Part_No
WHERE w.RowID BETWEEN q.theMin AND q.theMax
AND w.Child_Part_No LIKE 'F%';



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

jimmy2090
Starting Member

26 Posts

Posted - 2013-07-22 : 12:11:21
the original table:
https://lh4.googleusercontent.com/-phk2HELs2_Q/Ue1Y6sO03gI/AAAAAAAAKK4/DEeEURi1F3s/s490/Untitled.png

the table after update i want:
https://lh5.googleusercontent.com/-MMTB9BfiqMM/Ue1Y6p_NkeI/AAAAAAAAKK0/lf_FF548xys/s490/Untitled_.png

all the f_no will updated as the child_part_no if the child_part_no is start with f% until the next child_part_no is start with f% is appear
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-22 : 12:34:11
[code]
UPDATE t
SET t.f_NO = t1.child_part_no
FROM Table t
CROSS APPLY(SELECT TOP 1 child_part_no
FROM Table
WHERE ROWID <= t.ROWID
AND child_part_no LIKE 'F%'
ORDER BY ROWID DESC)t1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jimmy2090
Starting Member

26 Posts

Posted - 2013-07-22 : 12:35:13
here is the explanation:
https://lh4.googleusercontent.com/-4_bKxm4KVNo/Ue1fHIpqIuI/AAAAAAAAKLQ/VeaFI0OLOzA/s912/Untitled-2.gif
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-22 : 12:41:42
[code]-- Create staging table
CREATE TABLE #Stage
(
YakID INT IDENTITY(1, 1) NOT NULL,
FromRowID INT NOT NULL,
ToRowID INT NOT NULL,
PRIMARY KEY CLUSTERED
(
FromRowID,
ToRowID
),
Child_Part_No VARCHAR(10) NOT NULL
);

-- Populate staging table
INSERT #Stage
(
FromRowID,
ToRowID,
Child_Part_No
)
SELECT RowID AS FromRowID,
0 AS ToRowID,
Child_Part_No
FROM dbo.IPL
WHERE Child_Part_No LIKE 'F%'
AND (Parent_Block_No = '' OR Parent_Block_No IS NULL)
ORDER BY RowID;

-- Update the intervals
UPDATE s
SET s.ToRowID = ISNULL(w.FromRowID - 1, 2147483647)
FROM #Stage AS s
LEFT JOIN #Stage AS w ON w.YakID = s.YakID + 1

-- Do the final update
UPDATE i
SET i.f_No = s.Child_Part_No
FROM #Stage AS s
INNER JOIN dbo.IPL AS i ON i.RowID BETWEEN s.FromRowID AND s.ToRowID;[/code]Are you really sure rows with Child_Part_No starting with A should be overwritten?


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-22 : 12:49:10
[code]UPDATE i
SET i.f_No = f.Child_Part_No
FROM dbo.IPL AS i
CROSS APPLY (
SELECT TOP(1) w.Child_Part_No
FROM dbo.IPL AS w
WHERE w.RowID <= i.RowID
AND w.Child_Part_No LIKE 'F%'
AND (w.Parent_Block_No = '' OR w.Parent_Block_No IS NULL)
ORDER BY w.RowID DESC
) AS f;[/code]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

jimmy2090
Starting Member

26 Posts

Posted - 2013-07-22 : 21:46:50
thank you.
it solve my problem with a simple query.
it color my day. really appreciate it.
^^
Go to Top of Page
   

- Advertisement -