[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 241
  • Last Modified:

Delete with INNER JOINS

Hi,

I have the following SQL Query which does the update but for better performance i want to delete and then insert rather than updating, following is a update procedure, can anyone please give the query to Delete the records with following update criteria. With inner joins Deletion for me is getting little complex.

UPDATE Table1
SET
      first_name = MTab2.FirstName
      ,last_name = MTab2.LastName
      ,update_date = GETDATE()
      FROM Table2 MTab2
            INNER JOIN [Table3] Mtab3
                  ON MTab2.CompanyId = Mtab3.CompanyID AND Is_EPA != 1
            INNER JOIN [Table4]
                  ON [Table4].[id] = (SELECT id FROM Table4 WHERE id = Mtab3.Id AND RequiredEnt=1)
      WHERE
            MTab2.UpdateOn > (SELECT  MAX(Updated_date) FROM TABLE4)
            AND [Table1].[id] > 500


Thanks,
Rithesh
0
Star79
Asked:
Star79
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
delete Table1
FROM Table2 MTab2
    INNER JOIN [Table3] Mtab3
          ON MTab2.CompanyId = Mtab3.CompanyID AND Is_EPA != 1
    INNER JOIN [Table4]
          ON [Table4].[id] = (SELECT id FROM Table4 WHERE id = Mtab3.Id AND RequiredEnt=1)
WHERE
    MTab2.UpdateOn > (SELECT  MAX(Updated_date) FROM TABLE4)
    AND [Table1].[id] > 500
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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