Post reply

Best way to delete records from multiple tables

  • May 31, 2018 at 11:51 am

    #406348

    We have bunch of tables in our DB and there is no PK/FK relationship between those tables. I can join some tables based on ID, some on diff columns. The fact table contains users' information including the complete address with the country code. So  what's the best way to delete every single records for each person who lives in EU? Can I create a PK/FK relationship and delete the record from the fact table and it will delete all records from associated tables? I am not even sure if it will work? Any advise?

  • May 31, 2018 at 11:59 am

    #1992527

    You can still delete rows without a relationship in your database (although I still suggest you build that relationship).

    It might be quicker, however, to actually delete the records first, and then add your key constraints (as there's less for the data engine to check then). This is pseudo-SQL, however, you can write a DELETE statement with a JOIN like this:

    DELETE Cr
    FROM Customer Cr
      JOIN Country Cy ON Cr.CountryID = Cy.CountryID
      JOIN Continent Ct ON Cy.ContinentID = Ct.ContinentID
    WHERE Ct.ContinentCode = 'EU';

    Of course, you might not have a Continent code, so instead you'll need to use an IN for every country's code in the EU you have data for (which only you will know). That would start off along the lines of:

    DELETE Cr
    FROM Customer Cr
      JOIN Country Cy ON Cr.CountryID = Cy.CountryID
    WHERE Ct.CountryCode IN ('FR','DE','UK','ES',...,'IT');

    As a question, I assume you're deleting all this data due to GDPR? That seems a little overkill, if I'm honest.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

Viewing 2 posts - 1 through 1 (of 1 total)

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