Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2014
    Posts
    16

    Unanswered: Delete related records

    I am trying to delete the records in 2 related tables. The 'child' table has a field called [SETA],

    I want to delete all the records in this table that contain the same info, as well all the fields in the parent table that is related to this table. They share the [ID] field as key.

    This is my code:

    Code:
    DELETE    FROM Student a full outer JOIN Qualification b  on a.[ID] =b.[ID]  WHERE b.[SETA] = @SETA

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    use a select query and some kind of temporary data holder (table variable, temp table etc... ) to the list of IDs you want to delete. Then delete the child table by joining to that temp table and then delete the parent by joining to the child table.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2005
    Posts
    28
    Hi marius40,

    Sorry I could not understand which data you want to delete exactly

    But here is two DELETE statements that can help, I hope

    Code:
    declare @SETA varchar(10)
    set @SETA = 'SSRS'
    
    -- deletes only @SETA child records
    delete from Qualification where seta = @seta 
    
    -- deletes all child whose ID is related with @SETA variable
    delete from Qualification where id in (
    	select id from Qualification where seta = @seta 
    )

  4. #4
    Join Date
    Jan 2014
    Posts
    16

    replay

    Thank you

    That did the trick. The second delete was what I was looking for.

  5. #5
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

    This is minimal polite behavior on SQL forums.

    >> I am trying to delete the records sic: rows are not records! in 2 related tables. The 'child' [sic] table has a field [sic: columns are not fields] called SETA, <<

    All of this wrong. Child and parent are terms from hierarchical and network database, into RDBMS. We have referenced and referencing tables in SQL. In fact, we have cascaded DRI actions to do this!

    What is even worse is your magical, generic “id” column. There is no such thing in RDBMS. You can have a “<something in particular>_id”, but not Kabbalah numbers or exposed physical locators like IDENTITY.

    Since you did not bother to post DDL, here is my guess.

    CREATE TABLE Students -– you do have more than one?
    (student_id CHAR(10) NOT NULL PRIMARY KEY,
    .. );

    CREATE TABLE Qualifications
    (student_id CHAR(10) NOT NULL
    REFERENCES Students (student_id)
    ON DELETE CASCADE,
    qualification_code CHAR(5) NOT NULL,
    PRIMARY KEY (student_id, qualification_code),
    ..);

    When a row is deleted from the referenced table, the delete will cascade to the referencing tables.

    >> I want to delete all the records [sic] in this table that contain the same info, as well all the fields [sic] in the parent [sic] table that is related to this table. They share the “id” field [sic] as key. <<

    NO! You delete whole rows because a table is made of rows that have the same structure. You do not delete columns.

    This is my code:

    DELETE FROM Students
    WHERE seta = @in_seta;

    Unlike file systems (with records and fields), most of the work is done in DDL, not application code.

Posting Permissions

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