Clearing records where "IsActive" = False

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Clearing records where "IsActive" = False

Author

Topic

Mr_T
Starting Member

2 Posts

Posted - 2011-08-11 : 05:29:50

Hi Everyone,

We have a database where we don't delete anything. Every table has a standard column "IsActive" which has a default of True, and if we want to delete, we set this to False.

There are dozens of tables, with lots of relationships.

Consider the following as an example:

Customers have Invoices, Invoices have LineItems

I would like to write a script which would:

1) Check the LineItems, delete any where IsActive = False

2) Check the Invoices and Delete any where IsActive = False AND where there are no Child LineItems where IsActive = True

3) Check the Customers and Delete any Where the IsActive = False AND where there are no Child Invoices where IsActive = True

(You are probably thinking why would you want to have LineItems where IsActive = True, but where the Parent Invoice IsAcive = False. This is because the LineItem may be related to a different Parent such as DeliveryNote which has IsActive = True).

I could do this with a fixed script, but I would need to know the structure of the database, and write the script in the correct order, and work out all the dependencies myself. I'd also need to keep this up to date when the database changes. I also couldn't re-use this on another database if it had the same columns.

What I am thinking of, is a script where I could point it at any given table, and then it would search for children (and children of children etc) and clean it all the way from the bottom up.

I'd really appreciate if anyone could help me out with the bit that will identify all the related children (and children's children).

Thanks in advance

T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-11 : 07:48:57

1, simple delete with where condition on IsActive will do.
2, something like


DELETE i
FROM Invoices i
INNER JOIN (SELECT InvoiceID,COUNT(CASE WHEN IsActive=1 THEN LineItemID ELSE NULL END) AS ActLineCnt
FROM lineItems
GROUP BY InvoiceID
)li
ON li.InvoiceID = i.InvoiceID
WHERE ActLineCnt=0
AND l.IsActive='False'

3,same as 2 but using Customers and Invoices

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Mr_T
Starting Member

2 Posts

Posted - 2011-08-11 : 08:50:49

Thanks, but rather than writing a specific script for the specific tables, I was hoping to create a generic script which will work on any table in any database that has the same structure.

Essentially I'd like to be able to type EXEC SP_ClearData 'Table1' and then have the script work out what the children are and clean it all from the bottom up.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-11 : 09:38:56

That's a very very bad idea.

Never ambiguously delete data. Always do it explicitly. It's a little more work, but at least you will KNOW what's happening rather than hope.

You will want to have a look at sys.foreign_keys, but again, what you want to do causes unexplained data loss, introduces difficult to track down bugs and creates ambiguous code.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2011-08-11 : 10:38:03

That's a very very VERY bad idea.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

daveryan
Starting Member

1 Post

Posted - 2011-08-11 : 11:03:41

Terrible idea Mr T.
What were you thinking?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-11 : 11:10:36

why will your table change dynamically?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources