Delete data from parent table

Question

Delete data from parent table

@Patrick123

Say Hey Kid

Points: 705
More actions
April 11, 2017 at 12:43 pm

#329380

Hi,
Can we delete data from parent table (1 year data) and child tables(6 months data)??

Viewing 5 posts - 1 through 4 (of 4 total)

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

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

@Patrick123 - Tuesday, April 11, 2017 12:43 PM
Hi,
Can we delete data from parent table (1 year data) and child tables(6 months data)??

This not enough information for us to be able to answer.
You cannot break RI, if that is what you mean.
What makes you think you cannot do what you want to do?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

@Patrick123 Say Hey Kid Points: 705 More actions · Answer 2

@Patrick123 - Tuesday, April 11, 2017 12:43 PM
Hi,
Can we delete data from parent table (1 year data) and child tables(6 months data)??

DECLARE @created_date DATETIME
SET @created_date=CONVERT(VARCHAR(10),DATEADD(YEAR,-1,GETDATE()),101)

    DECLARE @sixmonths DATETIME
    SET @sixmonths=CONVERT(VARCHAR(10),DATEADD(MONTH,-6,GETDATE()),101)
    SELECT
            employee_id
            into #temp

    FROM
            dbo.employee WITH(NOLOCK)
    WHERE
          created_date <=@created_date

UNION

    SELECT
            employee_id
    FROM
            dbo.child WITH(NOLOCK)
    WHERE
          created_date <=@sixmonths

UNION

    SELECT
            employee_id
    FROM
            dbo.child1 WITH(NOLOCK)
    WHERE
          created_date <=@sixmonths

            DELETE e1
    FROM dbo.child1 e1 WITH(ROWLOCK)
    INNER JOIN dbo.#temp a WITH(NOLOCK)
    ON e1.employee_Id=a.employee_Id

    DELETE t1
    FROM dbo.child t1 WITH(ROWLOCK)
    INNER JOIN dbo.#temp a WITH(NOLOCK)
    ON t1.employee_Id=a.employee_Id

    DELETE a1
    FROM dbo.employee a1 WITH(ROWLOCK)
    INNER JOIN dbo.#temp a WITH(NOLOCK)
    ON a1.employee_Id=a.employee_Id

sgmunson SSC Guru Points: 110640 More actions · Answer 3

@Patrick123 - Tuesday, April 11, 2017 12:43 PM
Hi,
Can we delete data from parent table (1 year data) and child tables(6 months data)??

That depends. Are there foreign key constraints tying the tables together? If so, is deletion controlled in the foreign key constraint using CASCADE? You'll need to provide details - preferably table CREATE statements and a good description of exactly what constitutes the last year or 6 months of data for each table.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Phil Parkin SSC Guru Points: 246962 More actions · Answer 4

Pasting in a ton of code with no text description of why really isn't helpful.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin