[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • 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 DiegelCommented:
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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