help with delete query + 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

SQL Server 2005 Forums

Transact-SQL (2005)

help with delete query + join

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-12-07 : 08:40:14

Hi,

I have the following query, which is working fine, but I want to change it to reduce text input errors.

The current query is


DELETE tblinstantmessage WHERE messageFromID = @userID and message like @message + '%'

I want to change it so I don't actually pass the message string, of what I would like to delete, but rather a "instantMessageID" of the actual duplicate message, and go from there. So for example I would have something like this. I'd have to JOIN onto the table somehow, and delete columns where the JOINed table's message column is equal the message and from the same userID?

Make sense? Any help is very much appreciated..

thanks again !!

mike123



CREATE PROCEDURE [dbo].[delete_IM_ALL_sent_duplicate_ADMIN]
	(
	@userID int,
	@instantmessageID int
	)
AS SET NOCOUNT ON

DELETE tblinstantmessage WHERE messageFromID = @userID .....



CREATE TABLE [dbo].[tblInstantMessage](
	[InstantMessageID] [int] IDENTITY(1,1) NOT NULL,
	[MessageToID] [int] NULL,
	[MessageFromID] [int] NULL,
	[Message] [varchar](1000) NULL,
	[Date] [smalldatetime] NOT NULL,
	[Checked] [tinyint] NULL
	)
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-07 : 09:07:13

Why are you sendnig both UserId and InstantMessageID?
InstantMessageID is already primary key of that table...

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

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-12-07 : 09:11:43

Hi Peso,

You are correct, and one smart DBA I might add

.

I guess I don't need @userID passed as well. It would be smarter to just get that on the JOIN right?
I just want to make sure I don't delete ALL messages that have a duplicate message field. I want to limit it to duplicate messages from the same user.

Thanks again!
mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-07 : 09:12:14

[code]CREATE PROCEDURE dbo.delete_IM_ALL_sent_duplicate_ADMIN
(
@InstantMessageID INT
)
AS

SET NOCOUNT ON

DECLARE @Message VARCHAR(1000),
@MessageFromID INT

SELECT @Message = [Message],
@MessageFromID = MessageFromID
FROM tblInstantMessage
WHERE InstantMessageID = @InstantMessageID

SELECT @InstantMessageID = MAX(InstantMessageID)
FROM tblInstantMessage
WHERE MessageFromID = @MessageFromID
AND [Message] = @Message

DELETE u
FROM tblInstantMessage AS u
WHERE MessageFromID = @MessageFromID
AND [Message] = @Message
AND InstantMessageID < @InstantMessageID
AND [Date] >= DATEADD(MINUTE, -5, CURRENT_TIMESTAMP)[/code]

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

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-12-07 : 09:41:20

Hi Peso,

Sorry in my explanation I think I messed up by using the word duplicate, and made things more complicated for you. Poor wording, my apologizes.

I'm actually looking to delete all records from the user with the same message, I don't need to preserve any. I'm not sure what this means for the query, should I just remove the extra lines and have it look like this? Perhaps theres a better way.

Should I do it like this ?

Thanks once again,
mike123



CREATE PROCEDURE dbo.delete_IM_ALL_sent_duplicate_ADMIN
(
	@InstantMessageID INT
)
AS

SET NOCOUNT ON

DECLARE	@Message VARCHAR(1000),
	@MessageFromID INT

SELECT	@Message = [Message],
	@MessageFromID = MessageFromID
FROM	tblInstantMessage
WHERE	InstantMessageID = @InstantMessageID


DELETE	u
FROM	tblInstantMessage AS u
WHERE	MessageFromID = @MessageFromID
	AND [Message] = @Message

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-07 : 09:50:56

That will delete ALL messages with same content for same sender.
You will not even retain one copy.

In my code, I keep last copy of message and delete all other.
Also, I make sure that only copies within last 5 minutes (configurable) are deleted.

What if you want to delete a message with content "Hi"? With your code, you will delete ALL messages from same user with content "Hi". If that is what you want to do, use your rewrite of my suggestion.

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

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-12-07 : 09:59:57

quote:
Originally posted by Peso

What if you want to delete a message with content "Hi"? With your code, you will delete ALL messages from same user with content "Hi". If that is what you want to do, use your rewrite of my suggestion.

Yes this is what I wanted to do. I will follow your instructions on this.

Thanks again!
mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources