Delete certain records in table

  • 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.

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

  • 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

  • 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

  • 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) 😀

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply