Delete certain records in table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Delete certain records in table

Delete certain records in table

Rate Topic

Display Mode

Topic Options

Author

Message

Mr. Newbie

Posted Wednesday, July 29, 2009 11:16 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 29, 2009 6:32 PM
Points: 7, Visits: 30

Hello,

This is my first post in this forum. Hope you will have patience on me since I am not SQL expert.

I currently have been thinking, how can I delete records in a table that is being returned by a select statement?

SELECT  A.intFactoryID, RTRIM(A.strSAPAsset#) strSAPAsset# , RTRIM(A.strConnector) strConnector, A.lngDate, RTRIM(A.strShift) strShift, 
LTRIM(RTRIM(A.strAlarmText)) strAlarmText, A.intAlarmCnt
FROM dbo.tblAlarmMaster A
inner join
(select [intFactoryID],[strSAPAsset#],[lngDate],[strShift],[strAlarmText]
from [tblAlarmMaster]
group by [intFactoryID],[strSAPAsset#],[lngDate],[strShift],[strAlarmText], intAlarmCnt
having count([intFactoryID]) > 1) B
	on A.[intFactoryID] = B.[intFactoryID]
	AND A.[strSAPAsset#] = B.[strSAPAsset#]
	AND A.[lngDate] = B.[lngDate]
	AND A.[strShift] = B.[strShift]
	AND A.[strAlarmText] = B.[strAlarmText]
order by A.[intFactoryID],A.[strSAPAsset#],A.[lngDate],A.[strShift],A.[strAlarmText]

I currently have this select statement, what I want is that I would like to delete the records returned by this select statement on my same table
tblAlarmMaster.

The keys in my tables are, intFactoryID,strSAPAsset, lngDate,strShift and strAlarmText.

I am not sure how to create the delete statement part. I have just been using only certain simple delete statements until I was face with this task. Hope someone will give pointers. Thank you.

Post #762143

Ghanta

Posted Wednesday, July 29, 2009 11:23 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, January 03, 2012 11:53 AM
Points: 231, Visits: 698

Use the key to join the table to this subquery and delete .... one way to do that

Post #762146

drew.allen

Posted Wednesday, July 29, 2009 11:59 PM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 02, 2013 1:20 PM
Points: 1,235, Visits: 5,389

I prefer using

DELETE
FROM [TableName]
WHERE RowID IN (
    &lt;Subquery here&gt;
)

I like this, because it's very clear which table you are deleting rows from.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Post #762159

Amit-1103237

Posted Thursday, July 30, 2009 12:58 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, February 02, 2010 1:53 AM
Points: 19, Visits: 41

Allen really gave a very precise and to the point solution.

But Newbie as u seem to be new this can be helpful to you to certain extent.....

Delete from dbo.tblAlarmMaster
where intFactoryID in
(
SELECT A.intFactoryID
FROM dbo.tblAlarmMaster A
inner join
(select [intFactoryID],[strSAPAsset#],[lngDate],[strShift],[strAlarmText]
from [tblAlarmMaster]
group by [intFactoryID],[strSAPAsset#],[lngDate],[strShift],[strAlarmText], intAlarmCnt
having count([intFactoryID]) > 1) B
on A.[intFactoryID] = B.[intFactoryID]
AND A.[strSAPAsset#] = B.[strSAPAsset#]
AND A.[lngDate] = B.[lngDate]
AND A.[strShift] = B.[strShift]
AND A.[strAlarmText] = B.[strAlarmText]
order by A.[intFactoryID],A.[strSAPAsset#],A.[lngDate],A.[strShift],A.[strAlarmText])

Hope you will get the result as desired by you.

Thanks,
Amit

Post #762191

Mr. Newbie

Posted Thursday, July 30, 2009 2:59 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 29, 2009 6:32 PM
Points: 7, Visits: 30

Hi All,

Thanks to your inputs, I was able to do what I want.

DELETE TABLE
FROM TABLE
JOIN (SUBQUERY)

I think this was how I first perceived the SQL statements but I was surprised that there was a RowID field in MSSQL 2k5.

I googled for it and tried it also, its fast and concise. Thanks for your help again . I might post other questions though later (because of all of you being friendly) BigGrin

Post #762228

« Prev Topic | Next Topic »

Permissions