• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

How to delete the records fetched by this SQL Query

I want to delete records from RULE_PARAMETER_ARCHIVE which are fetched from below query.

      select a.versionid, a.rulesetid, a.rulearchid from RULE_PARAMETER_ARCHIVE a
      inner join
      (
      select versionid, rulesetid, rulearchid
      from RULE_SET_ARCHIVE
      where NOT exists
            (
            select NULL from RULE_SET_ARCHIVE M
            group by VersionId, RuleSetId
            having M.versionid = RULE_SET_ARCHIVE.VersionId
            and M.RuleSetID = RULE_SET_ARCHIVE.RuleSetId
            and max(M.RuleArchId) = RULE_SET_ARCHIVE.RuleArchId
            )
      ) b
      on       a.versionid = b.VersionId
            and a.RuleSetID = b.RuleSetId
            and a.RuleArchId = b.RuleArchId
0
unifi
Asked:
unifi
1 Solution
 
Faiga DiegelSr Database EngineerCommented:
Something like:

DELETE a
FROM RULE_PARAMETER_ARCHIVE a
      inner join
      ( select versionid, rulesetid, rulearchid
      from RULE_SET_ARCHIVE
      where NOT exists
            (select NULL from RULE_SET_ARCHIVE M
            group by VersionId, RuleSetId
            having M.versionid = RULE_SET_ARCHIVE.VersionId
            and M.RuleSetID = RULE_SET_ARCHIVE.RuleSetId
            and max(M.RuleArchId) = RULE_SET_ARCHIVE.RuleArchId)
      ) b   on       a.versionid = b.VersionId
            and a.RuleSetID = b.RuleSetId
            and a.RuleArchId = b.RuleArchId

0
 
chapmandewCommented:
delete a from RULE_PARAMETER_ARCHIVE a
      inner join
      (
      select versionid, rulesetid, rulearchid
      from RULE_SET_ARCHIVE
      where NOT exists
            (
            select NULL from RULE_SET_ARCHIVE M
            group by VersionId, RuleSetId
            having M.versionid = RULE_SET_ARCHIVE.VersionId
            and M.RuleSetID = RULE_SET_ARCHIVE.RuleSetId
            and max(M.RuleArchId) = RULE_SET_ARCHIVE.RuleArchId
            )
      ) b
      on       a.versionid = b.VersionId
            and a.RuleSetID = b.RuleSetId
            and a.RuleArchId = b.RuleArchId
0
 
unifiAuthor Commented:
You are going to like this :)... I was trying exact same query since a hour but I was missing "a" in this first line...

DELETE a FROM RULE_PARAMETER_ARCHIVE
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now