Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2008
    Posts
    24

    Unanswered: How do I mark row as inactive?

    Or logically delete data? For example in a customer table mark customer as inactive and the referencing data in other tables?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    ALTER TABLE dbo.customers
      ADD is_active BIT NOT NULL DEFAULT 1
    GO
    
    CREATE VIEW dbo.active_customers
      AS
    SELECT customer_id
         , customer_name
         , ...
    FROM   dbo.customers
    WHERE  is_active = 1
    GO
    Then you join to active_customers instead of customers when looking at referencing tables.
    Code:
    SELECT orders.*
    FROM   dbo.orders
     INNER
      JOIN dbo.active_customers
        ON orders.customer_id = customers.customer_id
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2008
    Posts
    24
    oh gosh thanks

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It might be worth experimenting with materialising that view (aka indexed views in SQL Server) for performance reasons.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yes indeedy

    Another reason I love SQL Server 2005!
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    Another reason I love SQL Server 2005!
    There in 2k too bro there in 2k too
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by pootle flump
    There in 2k too bro there in 2k too
    Wh-wh-wha!
    I guess I pretty much skipped 2000 6.5 to 90 is a big leap
    George
    Home | Blog

  8. #8
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by georgev
    Wh-wh-wha!
    I guess I pretty much skipped 2000 6.5 to 90 is a big leap
    H-o-o-o-l-y cr@p. That's a HUGE leap!



    Regards,

    hmscott
    Have you hugged your backup today?

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Lol - you bet on it. One day George is coming back with "um....sorry guys - I can't use your solution - 6.5 doesn't recognise 'TOP'", the next he's optimising recursive CTEs.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What can I say, I'm just that good
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •