How to delete child records from table tradecode that not have parent on tradeco

  • problem

    How to delete child records from table tradecode that not have parent on tradecode ?

    parent and child exist on table trade code based on table MappingCodeValue parent and child

    so i need to delete records from trade code table that not have parent on table trade code

    so according to my explain two rows 5,6 on trade code table will be deleted

    TradeCodeId  PartIdCodeType   CodeValue
    5 1444ECCS-URB AB666-URB
    6 1931ECCS-URB AB778-URB

    5 and 6 is child and not have parent rows as AB666-US and AB756-US

    so it wrong and i will delete it

    but another rows on trade code have parent and child according to table mappingcodevalue so that it is correct

    so how to write query delete rows that have rows that have child and not have parent from trade code

    based on value exist on mappingcodevalue

    drop table #MappingCodeValue
    drop table #TradeCode
    create table #MappingCodeValue
    (
    id int identity (1,1),
    ParentCodeType nvarchar(50),
    ParentCodeValue nvarchar(50),
    ChildCodeType nvarchar(50),
    ChildCodeValue nvarchar(50)
    )
    INSERT INTO #MappingCodeValue
    (ParentCodeType,ParentCodeValue,ChildCodeType,ChildCodeValue)
    VALUES
    ('ECCS-US','AB123-US','ECCS-URB','AB123-URB'),
    ('ECCS-US','AB555-US','ECCS-URB','AB555-URB'),
    ('ECCS-US','AB666-US','ECCS-URB','AB666-URB'),
    ('ECCS-US','AB778-US','ECCS-URB','AB778-URB')


    CREATE TABLE #TradeCode
    (
    TradeCodeId int identity(1,1),
    PartId int,
    CodeType nvarchar(50),
    CodeValue nvarchar(50)
    )
    insert into #TradeCode(PartId,CodeType,CodeValue)VALUES
    (1222,'ECCS-US','AB123-US'),
    (1255,'ECCS-US','AB555-US'),
    (1222,'ECCS-URB','AB123-URB'),
    (1255,'ECCS-URB','AB555-URB'),
    (1444,'ECCS-URB','AB666-URB'),
    (1931,'ECCS-URB','AB778-URB')
  • delete tc
    from
    #TradeCode tc
    where not exists
    (select 1 from #MappingCodeValue mcv
    where mcv.ChildCodeType=tc.CodeType
    and mcv.ChildCodeValue=tc.CodeValue);

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • I don't design schemas everyday, but when I do, I try to setup cascade updates/deletes correctly to make administration easier. I understand how cascades work, but I can never remember which table is which.

    For example, if I have two tables - Parent and Child - with a foreign key on Child that references Parent and has ON DELETE CASCADE, which records trigger a cascade and which records get deleted by the cascade? My first guess would be the Child records get deleted when Parent records are deleted, since Child records depend on Parent records, but the ON DELETE is ambiguous; it could mean delete the Parent record when the Child record is deleted, or it could mean delete the Child record when the Parent is deleted. So which is it?

    share it vidmate

  • I’m not sure what changes need to be made to fit this into your database language, but in general, what I understand you to want to do is something to the tune of:

    DELETE FROM #TradeCode

    WHERE CodeValue IN(

    SELECT ChildCodeValue

    FROM #MappingCodeValue

    LEFT JOIN #TradeCode

    ON ParentCodeValue = CodeValue

    WHERE CodeValue IS NULL

    VivaTV CYBERFLIX

     

  • With regards to using the "not exists " variant vs the "left join where key is null" : it all about the volume you expect to delete vs the set volume.

    how about

    Delete TC
    FROM #TradeCode TC
    LEFT JOIN  #MappingCodeValue MCV
    ON MCV.CodeValue = TC.ParentCodeValue
    Where MCV.CodeValue is null ;

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Below query will delete TradeCodeId 5 and 6, since these records does not have parent available in same table. Please check -

    ;WITH CTE AS (
    SELECT tc.TradeCodeId,tc.CodeValue
    ,COALESCE(mcv.ParentCodeValue,tc.CodeValue) AS ParentCodeValue
    FROM #TradeCode AS tc
    LEFT JOIN #MappingCodeValue AS mcv
    ON tc.CodeValue=mcv.ChildCodeValue
    )
    DELETE tc
    FROM #TradeCode AS tc
    INNER JOIN CTE AS parent
    LEFT JOIN CTE AS child
    ON parent.ParentCodeValue=child.CodeValue
    ON tc.TradeCodeId=parent.TradeCodeId
    WHERE child.TradeCodeId IS NULL

    Anand

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • Useful information

  • movieboxproappapk wrote:

    Useful information

    I can feel the spam getting ready to flow on this one... 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • The terms "child" and "parent" are not part of RDBMS; they come from the network databases and refer to pointer chain structures. In SQL we have "referenced" and "referencing" columns, along with DRI (declarative referential integrity) actions. There are also no such monstrosities as "<something>_code_value"; those post fixes are what are called attribute properties in the ISO 11179 and metadata standards. You have to decide whether you wanted a code or a value, but it cannot be this bizarre hybrid you just invented.

    You also don't seem to know that a table has to have a key. By definition. It is not an option. The proprietary identity table property is not an attribute by definition, so it can never be a key. But it is let's bad programmers write their SQL as if they were still in a sequential file system.

    If this is a hierarchical encoding scheme, then I suggest that you look up the nested set model for such things. You should also be using DRI actions to maintain the data integrity in your schema. You can Google this or get a copy of my book on Trees & Hierarchies in SQL for details

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • jcelko212 32090 wrote:

    The proprietary identity table property is not an attribute by definition, so it can never be a key. But it is let's bad programmers write their SQL as if they were still in a sequential file system.

    Heh... only by your definition, Joe.  According to math on the subject, it's just fine.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • This was removed by the editor as SPAM

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

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