July 29, 2009 at 11:16 pm
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.
July 29, 2009 at 11:23 pm
Use the key to join the table to this subquery and delete .... one way to do that
July 29, 2009 at 11:59 pm
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
July 30, 2009 at 12:58 am
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
July 30, 2009 at 2:59 am
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