SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 for loop SQL to update a table by variable
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jimmy2090
Starting Member

Malaysia
20 Posts

Posted - 07/22/2013 :  09:38:53  Show Profile  Reply with Quote
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.

Edited by - jimmy2090 on 07/22/2013 09:41:22

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 07/22/2013 :  09:50:02  Show Profile  Reply with Quote
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

Malaysia
20 Posts

Posted - 07/22/2013 :  10:02:04  Show Profile  Reply with Quote
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

Malaysia
20 Posts

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

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 07/22/2013 :  11:50:07  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Malaysia
20 Posts

Posted - 07/22/2013 :  12:11:21  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 07/22/2013 :  12:34:11  Show Profile  Reply with Quote

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


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

jimmy2090
Starting Member

Malaysia
20 Posts

Posted - 07/22/2013 :  12:35:13  Show Profile  Reply with Quote
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

Sweden
30281 Posts

Posted - 07/22/2013 :  12:41:42  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- 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;
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

Sweden
30281 Posts

Posted - 07/22/2013 :  12:49:10  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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;



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

jimmy2090
Starting Member

Malaysia
20 Posts

Posted - 07/22/2013 :  21:46:50  Show Profile  Reply with Quote
thank you.
it solve my problem with a simple query.
it color my day. really appreciate it.
^^
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000