help with update statement - 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

SQL Server 2005 Forums

Transact-SQL (2005)

help with update statement

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-02-28 : 04:54:30

Hi,

I have a forum that was based off the same design as sqlteam (snitz forums). I have been having a problem for quite sometime that I want to tackle.
I have posted the table structure below as you can see.

The problem is that when I delete a user, (thats held in a different table), it deletes all the users replies in the forums. This behaviour is fine, but the problem is that it leaves the column [T_REPLIES] in
[FORUM_TOPICS] with an inaccurate value.

What I would like to do is run a query every night that puts these values back in sync.

For example

how do I write this ? not sure, but something like this

--just a guess below here, i dont want to run it live and mess up results
UPDATE [FORUM_TOPICS] FT SET T_REPLIES = (SELECT COUNT(*) FROM [FORUM_REPLY] WHERE TOPIC_ID = FT.TOPIC_ID)

any help is greatly appreciated, thanks once again! :)

mike123

CREATE TABLE [dbo].[FORUM_TOPICS](
[CAT_ID] [tinyint] NOT NULL,
[FORUM_ID] [tinyint] NOT NULL,
[TOPIC_ID] [int] IDENTITY(1,1) NOT NULL,
[T_STATUS] [tinyint] NULL,
[T_MAIL] [tinyint] NULL,
[T_SUBJECT] [nvarchar](100) NULL,
[T_MESSAGE] [text] NULL,
[T_AUTHOR] [int] NULL,
[T_REPLIES] [int] NULL,
[T_VIEW_COUNT] [int] NULL,
[T_LAST_POST] [datetime] NULL,
[T_DATE] [datetime] NULL,
[T_LAST_POSTER] [int] NULL,
[T_IP] [nvarchar](50) NULL,
[T_ARCHIVE_FLAG] [tinyint] NULL,
[T_LAST_POST_AUTHOR] [int] NULL
)

CREATE TABLE [dbo].[FORUM_REPLY](
[CAT_ID] [tinyint] NOT NULL,
[FORUM_ID] [tinyint] NOT NULL,
[TOPIC_ID] [int] NOT NULL,
[REPLY_ID] [int] IDENTITY(1,1) NOT NULL,
[R_MAIL] [tinyint] NULL,
[R_AUTHOR] [int] NULL,
[R_MESSAGE] [text] NULL,
[R_DATE] [datetime] NULL,
[R_IP] [nvarchar](50) NULL,
[R_STATUS] [tinyint] NULL
)

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-28 : 06:21:42

try this

UPDATE FT
SET FT.T_REPLIES = FR.CNT
FROM [FORUM_TOPICS] FT
INNER JOIN
(SELECT COUNT(*) AS CNT,TOPIC_ID FROM [FORUM_REPLY]
GROUP BY TOPIC_ID ) FR
WHERE FR.TOPIC_ID = FT.TOPIC_ID)

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-02-28 : 16:34:01

hmm..... I this looks pretty good to me but I can't figure out the syntax error I am getting...

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'WHERE'.

Any idea? Thanks once again!

much appreciated,
mike123

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-28 : 19:12:22

[code]UPDATE FT
SET FT.T_REPLIES = FR.CNT
FROM [FORUM_TOPICS] FT
INNER JOIN
(SELECT COUNT(*) AS CNT,TOPIC_ID FROM [FORUM_REPLY]
GROUP BY TOPIC_ID ) FR
ON FR.TOPIC_ID = FT.TOPIC_ID[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources