Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Delete certain records in table Expand / Collapse
Author
Message
Posted Wednesday, July 29, 2009 11:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, July 29, 2009 11:23 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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
Posted Wednesday, July 29, 2009 11:59 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 03, 2013 8:24 AM
Points: 1,240, Visits: 5,421
I prefer using
DELETE
FROM [TableName]
WHERE RowID IN (
<Subquery here>
)

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
Posted Thursday, July 30, 2009 12:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Thursday, July 30, 2009 2:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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)
Post #762228
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse