Results 1 to 3 of 3

Thread: How to delete records from table trade code that No have parent code value and parent

  1. #1
    Join Date
    Jul 2017
    Posts
    3

    How to delete records from table trade code that No have parent code value and parent

    I work on SQL server 2012 I need to delete rows that not have parent code type and parent code value
    Code:
    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'),
     ('HTS-US','AB900-US','SCHEDUALB','AB900-URB')
    
     --select * from #MappingCodeValue
     CREATE TABLE #TradeCode
     (
     TradeCodeId int identity(1,1),
     PartId  int,
     CodeType  nvarchar(50),
     CodeValue nvarchar(50),
     PartDone  bit
     )
     insert into #TradeCode(PartId,CodeType,CodeValue,PartDone)VALUES
     (1222,'ECCS-US','AB123-US',null),
     (1255,'ECCS-US','AB555-US',null),
     (1222,'ECCS-URB','AB123-URB',1),
     (1255,'ECCS-URB','AB555-URB',1),
     (1444,'ECCS-URB','AB666-URB',1),
     (1931,'ECCS-URB','AB778-URB',1),
     (7000,'ECCS-URB','AB778-URB',1),
     (8000,'ECCS-URB','AB778-URB',1),
     (800,'SCHEDUALB','AB900-URB',1),
     (900,'SCHEDUALB','AB900-URB',1),
     (1255,'HTS-US','AB900-US',null),
     (909,'HTS-US','AB900-US',null)
    what i try is

    Code:
    ;with mycte as (
     select Partid from #TradeCode t where PartDone=1
     group by Partid 
    having (count(*)=1)
    )
    
    delete t 
    from  #TradeCode t 
    join mycte m on m.Partid=t.Partid
    
    
    select * from #TradeCode
    Expected result after delete
    Code:
    TradeCodeId    PartId	CodeType	CodeValue	PartDone
    1	        1222	ECCS-US  	 AB123-US	   null
    2	        1255	ECCS-US	         AB555-US	   null
    3	        1222	ECCS-URB	 AB123-URB   	   1
    4	        1255	ECCS-URB	 AB555-URB	   1
    11	        1255	HTS-US	         AB900-US	   null
    12	        909	HTS-US	         AB900-US	   null

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    I do not understand the requirement when you say - rows that not have parent code type and parent code value

    For the sample you posted all records have parent code type and value

  3. #3
    Join Date
    Aug 2022
    Posts
    22
    You can use a DELETE statement with a subquery. The subquery would be used to select the records that do not have a parent code value and parent, and the DELETE statement would then remove those records from the table.

    Example of how the SQL query would look:

    DELETE FROM trade
    WHERE NOT EXISTS (SELECT 1 FROM trade WHERE parent_code IS NOT NULL AND parent IS NOT NULL);
    This query will delete all records from the "trade" table where the parent_code column is NULL and the parent column is NULL.

    You can also use this query to delete records where the parent_code and parent value is empty

    DELETE FROM trade
    WHERE (parent_code ='' OR parent_code IS NULL) AND (parent ='' OR parent IS NULL);

Tags for this Thread

Posting Permissions

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