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 intdeclare @CHILD_PART_NO Varchar (10)SELECT @CHILD_PART_NO = CHILD_PART_NO, @root_ID = ROWID FROM IPLWHERE 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 intdeclare @CHILD_PART_NO Varchar (10)SELECT @CHILD_PART_NO = CHILD_PART_NO, @root_ID = ROWID FROM IPLWHERE 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 |
|
|
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 nextupdate 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) |
|
|
jimmy2090
Starting Member
26 Posts |
Posted - 2013-07-22 : 11:37:42
|
any idea? please help...thank you in advance... |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-22 : 11:50:07
|
Something like this?UPDATE wSET w.f_NO = w.Child_Part_NoFROM dbo.IPL AS wINNER 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_NoWHERE 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 |
|
|
jimmy2090
Starting Member
26 Posts |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-22 : 12:34:11
|
[code]UPDATE tSET t.f_NO = t1.child_part_no FROM Table tCROSS 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-22 : 12:41:42
|
[code]-- Create staging tableCREATE 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 tableINSERT #Stage ( FromRowID, ToRowID, Child_Part_No )SELECT RowID AS FromRowID, 0 AS ToRowID, Child_Part_NoFROM dbo.IPLWHERE Child_Part_No LIKE 'F%' AND (Parent_Block_No = '' OR Parent_Block_No IS NULL)ORDER BY RowID;-- Update the intervalsUPDATE sSET s.ToRowID = ISNULL(w.FromRowID - 1, 2147483647)FROM #Stage AS sLEFT JOIN #Stage AS w ON w.YakID = s.YakID + 1-- Do the final updateUPDATE iSET i.f_No = s.Child_Part_NoFROM #Stage AS sINNER 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 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-22 : 12:49:10
|
[code]UPDATE iSET i.f_No = f.Child_Part_NoFROM dbo.IPL AS iCROSS 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 |
|
|
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.^^ |
|
|
|