update inner join - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

update inner join

Author

Topic

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-09-26 : 10:02:39

hi i need to do an update

i have a select which sets @ordinal
@ordinal could be 25

now i need to update each record starting at 25 adding 1 each time

update treenode
INNER JOIN Page p on tn.TreeNodeId = p.TreeNodeId
set ordinal = @ordinal +1
WHERE treenode.ParentTreeNodeID = @ParentId AND p.PageClassId = @PageClassId

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 10:05:11

Using SQL Server 2005 or SQL Server 2000?

E 12°55'05.63"
N 56°04'39.26"

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-09-26 : 10:05:34

Using SQL Server 2005

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 10:07:24

[code]SET @ordinal = 25 - 1

UPDATE tn
SET @ordinal = tn.ordinal = @ordinal + 1
FROM treeNode AS tn
INNER JOIN page AS p ON p.treeNodeID = tn.treeNodeID
WHERE tn.parentTreeNodeID = @parentID
AND p.pageClassID = @pageClassID[/code]

E 12°55'05.63"
N 56°04'39.26"

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-09-26 : 10:20:00

ok i forgot to add the order by clause
i have tried

UPDATE tn
SET @ordinal = @ordinal + 1
FROM treeNode AS tn
INNER JOIN page AS p ON p.treeNodeID = tn.treeNodeID
WHERE tn.parentTreeNodeID = @parentID AND p.pageClassID = @pageClassID
ORDER BY p.Title

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 10:23:39

First, your copy of my code is not complete.
Second, you can't have a ORDER BY case in an update.

However you can "cheat" with ROW_NUMBER() function.

UPDATE	f
SET	f.ordinal = f.recID + 24
FROM	(
		SELECT		tn.ordinal,
				ROW_NUMBER() OVER (ORDER BY p.title) AS recID
		FROM		treeNode AS tn
		INNER JOIN	page AS p ON p.treeNodeID = tn.treeNodeID
		WHERE		tn.parentTreeNodeID = @parentID
				AND p.pageClassID = @pageClassID
	) AS f

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 10:29:13

[code]DECLARE @tableA TABLE
(
ID INT,
ordinal INT
)

INSERT @tableA
(
ID
)
SELECT 10 UNION ALL
SELECT 20 UNION ALL
SELECT 30

DECLARE @tableB TABLE
(
ID INT,
title INT
)

INSERT @tableB
SELECT 10, 2 UNION ALL
SELECT 30, 1

UPDATE f
SET f.ordinal = f.recID + 24
FROM (
SELECT a.ordinal,
ROW_NUMBER() OVER (ORDER BY b.title) AS recID
FROM @tableA AS a
INNER JOIN @tableB AS b ON b.ID = a.ID
) AS f

SELECT *
FROM @tableA

SELECT *
FROM @tableB[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources