How to delete child records?

Transact-SQL
1

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  PartId	CodeType   CodeValue	
5	     1444	ECCS-URB    AB666-URB	          
6	     1931	ECCS-URB    AB778-URB

5 and 6 is child and not have parent rows as AB666-US and AB778-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')
2

Before answering that question, how was it that child records were created without parent records? And what will prevent this from happening again so you do not have to do cleanup job? Are you missing foreign key constraints? Please answer these first?

3

No idont have foreign key .
so what is your opinion to make this job clean

4

If you answer each of my questions we can figure out how to get a clean job

5

so what i do from your opinion to do is clean

6

your design of these tables is a bit confusing to me. So which table is parent? TradeCode or MappingCodeValue?

7

hi

hope this helps :slight_smile:

image

delete from TableB where parent in ( 1, 2 )  .. ids from TableA
8

Give a man a fish and you feed him for a day; teach a man to fish and you feed him for a lifetime.

While that DML might solve his problem now, it does not help him in the long run to design a more robust system and to get to the root of the issue. aka child rows with no parent rows. He needs to fix the issue by implementing a better design.

9

Thanks, Yosaisz

Don't know what his needs are ...
Maybe this is one time thing only ...

If he wants to learn to design
Google search
You tube videos

Are there a plenty :stuck_out_tongue_winking_eye::stuck_out_tongue_winking_eye:

1 Like
10

Yes those are also available but I think we can also help him. This forum is more than just DML forum.
We can help elevate other peoples design skills as well

1 Like