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

Question

Post reply

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

ahmed_elbarbary.2010

Hall of Fame

Points: 3118

April 11, 2020 at 9:58 pm

#3742241

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')

This topic was modified 4 years ago by ahmed_elbarbary.2010.

October 12, 2020 at 7:47 am

This was removed by the editor as SPAM
November 18, 2020 at 3:42 pm

This was removed by the editor as SPAM
December 17, 2020 at 4:21 pm

This was removed by the editor as SPAM
January 16, 2021 at 7:59 am

This was removed by the editor as SPAM
March 24, 2021 at 1:27 pm

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

Steve Collins SSCrazy Points: 2069 More actions · Answer 1

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

bhaiterabhai Newbie Points: 3 More actions · Answer 2

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

markkevin123 Newbie Points: 1 More actions · Answer 3

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

Johan Bijnens SSC Guru Points: 135259 More actions · Answer 4

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

Anand929 Grasshopper Points: 22 More actions · Answer 5

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

movieboxproappapk Newbie Points: 1 More actions · Answer 6

movieboxproappapk

Newbie

Points: 1

March 6, 2021 at 10:09 pm

#3854368

Useful information

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 7

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.

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

jcelko212 32090 SSCrazy Eights Points: 9294 More actions · Answer 8

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.

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 9

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.

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