Simple Query Help - 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)

Simple Query Help

Author

Topic

drdream
Starting Member

29 Posts

Posted - 2008-08-13 : 10:45:06

Hello.. Many thanks for any help in advance.. I've been doing so much .NET Development that simple query tasks elude me. Im trying to build a facebook like interface for changes to a website. I've got all the stuff in place populating to a separate table.

EntryID, DateTime, Entry, UserID
1 8/2/2008 14:22:21 'Mike Changed His Profile Picture' 334
2 7/31/2008 12:12:32 'Sally changed contact info' 445
3 7/31/2008 12:12:11 'Sally changed contact info' 445
4 7/30/2008 11:30:11 'Bob added new photos' 334
5 7/30/2008 11:30:09 'Bob added new photos' 334
6 7/30/2008 10:33:11 'Steve joined the site' 443

The problem is people often make several changes to their profile.. I only want to say it once. I want to somehow remove those duplicate entries either as a batch or query-time. I've tried SELECT DISTICT, GROUP BY Entry but I still get the duplicates. I know this is wicked easy. Please help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 10:51:38

[code]DECLARE @Sample TABLE
(
EntryID INT,
dt DATETIME,
[Entry] VARCHAR(MAX),
UserID INT
)

INSERT @Sample
SELECT 1, '8/2/2008 14:22:21' , 'Mike Changed His Profile Picture', 334 UNION ALL
SELECT 2, '7/31/2008 12:12:32', 'Sally changed contact info' , 445 UNION ALL
SELECT 3, '7/31/2008 12:12:11', 'Sally changed contact info' , 445 UNION ALL
SELECT 4, '7/30/2008 11:30:11', 'Bob added new photos' , 334 UNION ALL
SELECT 5, '7/30/2008 11:30:09', 'Bob added new photos' , 334 UNION ALL
SELECT 6, '7/30/2008 10:33:11', 'Steve joined the site' , 443

SELECT *
FROM @Sample

DELETE f
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY UserID, [Entry] ORDER BY dt DESC) AS RecID
FROM @Sample
) AS f
WHERE RecID > 1

SELECT *
FROM @Sample[/code]

E 12°55'05.25"
N 56°04'39.16"

drdream
Starting Member

29 Posts

Posted - 2008-08-13 : 10:54:42

I knew it had something to do with the new Partition By in SQL Server 2005. I haven't tried it yet but its beautiful. Many thanks Peso for people like you!

drdream
Starting Member

29 Posts

Posted - 2008-08-13 : 11:11:14

Ummm.. Running that twice wiped out all the historical data.. (its ok since its constantly being added.. but.. I need it to be run all the time?

drdream
Starting Member

29 Posts

Posted - 2008-08-13 : 11:14:45

Not sure why but when doing it on your sample.. it doesnt wipe out the data.. All I did was modify the delete part and put it in a stored procedure..

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE sp_RemoveDuplicateHistory ()
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
DELETE f
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY UserID, [Entry] ORDER BY [DateTime] DESC) AS RecID
FROM tbl_UserChanges
) AS f

WHERE RecID > 1
END
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 11:16:35

How can you tell it doesn't delete records?
Did the sample code I provided work for you?

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 11:17:16

what was result when you run the procedure? have you had duplicate data on your table when this was run?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 11:22:01

You can either have the delete as a trigger like this

CREATE TRIGGER	trg_RemoveDuplicateHistory
ON		tbl_UserChanges
AFTER		UPDATE,
		INSERT
AS

SET NOCOUNT ON

DELETE	f
FROM	(
		SELECT		ROW_NUMBER() OVER (PARTITION BY UserID, [Entry] ORDER BY [DateTime] DESC) AS RecID
		FROM		tbl_UserChanges AS uc
		INNER JOIN	inserted AS i ON i.PkCol = uc.PkCol
	) AS f
WHERE	RecID > 1

Or keep it as a stored procedure and have a job running every 15 minutes or so executing the stored procedure.

E 12°55'05.25"
N 56°04'39.16"

drdream
Starting Member

29 Posts

Posted - 2008-08-13 : 12:03:57

Ok your code seems to be working.. There is another I guess unrelated problem.. Actually I think it is related becuase im not sure of the relationship between the INSERTED table in Triggers.. Right Now I Have this trigger when adding a mix

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[tr_MixAdded]
ON [dbo].[Mixes]
FOR INSERT
AS
--PRINT GETDATE()
DECLARE @MixID int
DECLARE @MixTitle varchar(200)
DECLARE @UserID int
DECLARE @DisplayName varchar(200)
DECLARE @UserName varchar(50)
DECLARE @MixURL varchar(max)

SELECT @UserID =(SELECT CustID FROM INSERTED)
SELECT @DisplayName = (SELECT DisplayName from CustInfo c Where C.CustID = @UserID)
SELECT @UserName = (SELECT UserName from CustInfo c Where C.CustID = @UserID)
SELECT @MixID = (SELECT MixID From INSERTED)
SELECT @MixTitle = (Select MixTitle from inserted)

SET @MixURL = '/' + @UserName + '/mixes/' + CAST(@MixID as varchar(10)) + '.aspx'

INSERT INTO tbl_UserChanges([DateTime],Entry, UserID)
VALUES (GetDate(),
@DisplayName + ' added a new mix <a href="' + @MixURL + '">' + @MixTitle + '</a>.',
@UserID)

I omitted the "INSERTED" stuff and now it broke the application cause the normal insert procedure doesn't give back the @@IDENTITY column. Is the INSERTED Table required in an insert trigger? Like do you have to do the "INNER JOIN inserted AS i ON i.PkCol = uc.PkCol"

If so your code gave me an error saying that i.PkCol is not recognized. I changed that to EntryID cause thats the PK but then I get another error. Something to the effect of 'Ambiguous column name UserID', 'Ambiguous Column Name Entry', etc

So Theres two problems. One is with the code above to actually perform the insert, and two with your sample (thanks) and the ambiguous columns.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 12:10:14

That may be because you're using * and you've same columns coming INSERTED as well as other table. so try replacing * with actual column names.

drdream
Starting Member

29 Posts

Posted - 2008-08-13 : 12:14:07

Not sure I understand.. I dont see a SELECT * in the code sample above

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 12:30:06

may be this:-

CREATE TRIGGER	trg_RemoveDuplicateHistory
ON		tbl_UserChanges
AFTER		UPDATE,
		INSERT
AS

SET NOCOUNT ON

DELETE	f
FROM	(
		SELECT		ROW_NUMBER() OVER (PARTITION BY uc.UserID, uc.[Entry] ORDER BY [DateTime] DESC) AS RecID
		FROM		tbl_UserChanges AS uc
		INNER JOIN	inserted AS i ON i.EntryID = uc.EntryID
	) AS f
WHERE	RecID > 1

drdream
Starting Member

29 Posts

Posted - 2008-08-13 : 13:02:08

Thanks for all your help guys but I was getting this error even with Peso's Code.. After adding that trigger, then inserting a mix .NET returns

"View or function 'f' is not updatable because the modification affects multiple base tables."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 13:03:43

is tbl_UserChanges a view?

drdream
Starting Member

29 Posts

Posted - 2008-08-13 : 13:17:08

tbl_UserChanges is a normal table

drdream
Starting Member

29 Posts

Posted - 2008-08-13 : 13:17:53

tbl_UserChanges is a simple table with 4 columns.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 13:20:28

quote:
Originally posted by drdream

tbl_UserChanges is a simple table with 4 columns.

then wat's the view you're trying to update thats causing the posted error?If you want to update view, one way to do it is to create an instead of update trigger on view and write code inside trigger to update base tables instead of view itself.

drdream
Starting Member

29 Posts

Posted - 2008-08-13 : 13:34:16

Im not using Views in any of my code. The error trips on a simple update query. Im posting my original trigger below

-- // ORIGINAL TRIGGER // --

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tr_MixChanged]
ON [dbo].[Mixes]
AFTER UPDATE
AS

IF NOT UPDATE(Tracklisting) AND NOT UPDATE(MixTitle)
RETURN

DECLARE @OldTrack varchar(Max)
DECLARE @NewTrack varchar(Max)
DECLARE @MixID int
DECLARE @MixTitle varchar(200)
DECLARE @UserID int
DECLARE @DisplayName varchar(200)
DECLARE @UserName varchar(50)
DECLARE @MixURL varchar(max)

SELECT @UserID =(SELECT CustID FROM INSERTED)
SELECT @DisplayName = (SELECT DisplayName from CustInfo c Where C.CustID = @UserID)
SELECT @UserName = (SELECT UserName from CustInfo c Where C.CustID = @UserID)
SELECT @MixID = (SELECT MixID From INSERTED)
SELECT @MixTitle = (Select MixTitle from inserted)

SET @MixURL = '/' + @UserName + '/mixes/' + CAST(@MixID as varchar(10)) + '.aspx'

-- // TRACKLISTING CHANGED -- //
IF UPDATE (TrackListing)
BEGIN
SELECT @OldTrack = d.trackListing FROM deleted d

SELECT @NewTrack = i.trackListing FROM inserted i

IF @OldTrack <> @NewTrack
BEGIN
INSERT INTO tbl_UserChanges([DateTime],Entry, UserID)
VALUES (GetDate(),
@DisplayName + ' updated tracklisting for <a href="' + @MixURL + '">' + @MixTitle + '</a>.',
@UserID)
--PRINT @UserName + ' updated Tracklisting from ' + @OldTrack + ' to ' + @NewTrack
END

UPDATE Mixes Set TrackListing = @NewTrack FROM Mixes M
JOIN Inserted i on i.MixID = M.MixID Where M.MixID = @MixID
END

-- // MIX TITLE CHANGED -- //
IF UPDATE (MixTitle)
BEGIN
DECLARE @OldMixTitle varchar(max)
DECLARE @NewMixTitle varchar(max)

SELECT @OldMixTitle = d.MixTitle FROM deleted d
SELECT @NewMixTitle = i.MixTitle FROM inserted i

IF @OldMixTitle <> @NewMixTitle
BEGIN
INSERT INTO tbl_UserChanges([DateTime],Entry, UserID)
VALUES (GetDate(),
@DisplayName + ' updated the Title for <a href="' + @MixURL + '">' + @MixTitle + '</a>.',
@UserID)
-- PRINT @UserName + ' updated Title from ' + @OldMixTitle + ' to ' + @NewMixTitle
END

UPDATE Mixes Set MixTitle = @NewMixTitle FROM Mixes M
JOIN Inserted i on i.MixID = M.MixID Where M.MixID = @MixID
END

-- // INSERT TRIGGER (SEPARATE OBJECT) --//

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[tr_MixAdded]
ON [dbo].[Mixes]
FOR INSERT
AS
--PRINT GETDATE()
DECLARE @MixID int
DECLARE @MixTitle varchar(200)
DECLARE @UserID int
DECLARE @DisplayName varchar(200)
DECLARE @UserName varchar(50)
DECLARE @MixURL varchar(max)

SELECT @UserID =(SELECT CustID FROM INSERTED)
SELECT @DisplayName = (SELECT DisplayName from CustInfo c Where C.CustID = @UserID)
SELECT @UserName = (SELECT UserName from CustInfo c Where C.CustID = @UserID)
SELECT @MixID = (SELECT MixID From INSERTED)
SELECT @MixTitle = (Select MixTitle from inserted)

SET @MixURL = '/' + @UserName + '/mixes/' + CAST(@MixID as varchar(10)) + '.aspx'

INSERT INTO tbl_UserChanges([DateTime],Entry, UserID)
VALUES (GetDate(),
@DisplayName + ' added a new mix <a href="' + @MixURL + '">' + @MixTitle + '</a>.',
@UserID)

All are tables, no views. This is the complete Trigger Code for INSERTS/UPDATES. All other SQL is just simple "INSERT INTO Table" or "UPDATE TABLE" from .net

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 13:41:25

One major issue with the posted trigger is that you're assuming it will process only one row at a time but you cant guarantee tht will be the case always. So statements like below

SELECT @OldTrack = d.trackListing FROM deleted d

will not work as expected as it will return more than 1 value but variable can hold only one time at a time.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 14:15:45

[code]CREATE TRIGGER trg_RemoveDuplicateHistory
ON tbl_UserChanges
AFTER UPDATE,
INSERT
AS

SET NOCOUNT ON

DELETE f
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY uc.UserID, uc.Entry ORDER BY uc.DateTime DESC) AS RecID
FROM tbl_UserChanges AS uc
WHERE EXISTS (SELECT * FROM inserted AS i ON i.UserID = uc.UserID)
) AS f
WHERE RecID > 1[/code]

E 12°55'05.25"
N 56°04'39.16"

drdream
Starting Member

29 Posts

Posted - 2008-08-14 : 11:44:40

Thanks so much.. That trigger is now working. The reason I was getting errors before.. Was specifically related to the @@IDENTITY use. I found an article that stated "Use of @@Identity can cause nasty bugs with triggers" .. This is indeed what happened. I have since switched to SCOPE_IDENTITY() to get the newly inserted PK. http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/ . Many thanks for your help

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources