InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 23:40:49 GMT20http://www.sqlservercentral.com/Forums/Topic762143-338-1.aspxHi All,Thanks to your inputs, I was able to do what I want.[code]DELETE TABLEFROM TABLEJOIN (SUBQUERY)[/code]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) :-DThu, 30 Jul 2009 02:59:45 GMTMr. Newbiehttp://www.sqlservercentral.com/Forums/Topic762143-338-1.aspxAllen 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.tblAlarmMasterwhere intFactoryID in (SELECT A.intFactoryIDFROM dbo.tblAlarmMaster Ainner join(select [intFactoryID],[strSAPAsset#],[lngDate],[strShift],[strAlarmText]from [tblAlarmMaster]group by [intFactoryID],[strSAPAsset#],[lngDate],[strShift],[strAlarmText], intAlarmCnthaving 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,AmitThu, 30 Jul 2009 00:58:40 GMTAmit-1103237http://www.sqlservercentral.com/Forums/Topic762143-338-1.aspxI prefer using [code="sql"]DELETEFROM [TableName]WHERE RowID IN ( <Subquery here>)[/code]I like this, because it's very clear which table you are deleting rows from.DrewWed, 29 Jul 2009 23:59:04 GMTdrew.allenhttp://www.sqlservercentral.com/Forums/Topic762143-338-1.aspxUse the key to join the table to this subquery and delete .... one way to do thatWed, 29 Jul 2009 23:23:27 GMTGhantahttp://www.sqlservercentral.com/Forums/Topic762143-338-1.aspxHello,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?[code]SELECT A.intFactoryID, RTRIM(A.strSAPAsset#) strSAPAsset# , RTRIM(A.strConnector) strConnector, A.lngDate, RTRIM(A.strShift) strShift, LTRIM(RTRIM(A.strAlarmText)) strAlarmText, A.intAlarmCntFROM dbo.tblAlarmMaster Ainner join(select [intFactoryID],[strSAPAsset#],[lngDate],[strShift],[strAlarmText]from [tblAlarmMaster]group by [intFactoryID],[strSAPAsset#],[lngDate],[strShift],[strAlarmText], intAlarmCnthaving 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][/code]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.Wed, 29 Jul 2009 23:16:40 GMTMr. Newbie