Is it possible to do a query on UPDATE? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

Is it possible to do a query on UPDATE?

Author

Topic

FruitBatInShades
Yak Posting Veteran

51 Posts

Posted - 2009-11-23 : 07:43:12

I am trying to change a tables ordering. Position is the number by which I'm ordering the results. In certain circumstances the natural position and the position set in the db do not match. I am trying to update them to their natural position.
I can get the details I want with the following SQL, but how can I do this processing on an UPDATE so I can set the correct positions?

SELECT UniqueId, ParentId, Name, Lineage, Position,
	/* Need to get the row position, Can use ROW_NUMBER in 2005 but we don;t have it :( */	
	(SELECT COUNT(*) + 1 FROM Page as p2 WHERE 
			LEFT(lineage, LEN(@Lineage)) = @Lineage 
				AND Depth = @Depth
				AND p2.lineage< p1.lineage
		) as NaturalPosition
 FROM Page as p1
	WHERE LEFT(lineage, LEN(@Lineage)) = @Lineage AND 
		Depth = @Depth
Order By Lineage, Position


68	64	Item 1	/160/264/068/	0	1
69	64	Item 2	/160/264/069/	0	2
70	64	Item 3	/160/264/070/	0	3
71	64	Item 4	/160/264/371/	3	4
67	64	Item 5	/160/264/567/	5	5
92	64	Item 6	/160/264/692/	6	6

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-11-23 : 07:56:24

Maybe this? (assuming you want to update position with the column you are generating)


UPDATE np SET
	position = r.naturalPosition
FROM
	page np

	JOIN (
		SELECT
			UniqueId
			, ParentId
			, Name
			, Lineage
			, Position
			, (SELECT COUNT(*) + 1 FROM Page as p2 WHERE LEFT(lineage, LEN(@Lineage)) = @Lineage AND Depth = @Depth AND p2.lineage< p1.lineage) as NaturalPosition
		 FROM
			Page as p1
		WHERE
			LEFT(lineage, LEN(@Lineage)) = @Lineage
			AND Depth = @Depth
		)
		r ON r.uniqueId = np.uniqueId

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

FruitBatInShades
Yak Posting Veteran

51 Posts

Posted - 2009-11-23 : 08:40:03

Thanks Charlie, will try it out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources