locked
How do I delete records from two tables with only one relationship RRS feed

  • Question

  • Hello,

    I have two tables that has one relationship, that is the IncidentID.

    I need to delete records from IncidentBase and also from incidentextensionbase , the only column they have in common is the IncidentID.

    The ticket number is in IncidentBase table but not in incidentextensionbase table, but incidentextensionbase table has some related information about the ticket numbers in IncidentBase table.

     I want the records to delete from IncidentBase and to also delete related information from incidentextensionbase.

    I tried this query:

    DELETE IncidentBase,incidentextensionbase

    from IncidentBase

    INNER JOIN incidentextensionbase on (IncidentBase.IncidentID = incidentextensionbase.IncidentID)

    where TicketNumber IN ('cas-023', 'cas-028', 'cas-031')

    I got errors, please help!

    • Moved by Olaf HelperMVP Tuesday, December 23, 2014 9:53 AM Moved from "Database Engine" to a more related forum.
    Tuesday, December 23, 2014 9:47 AM

Answers

  • First select into a temp table records you want to delete.

    secondly don't try deleting all 3 ticket numbers in one go. do it one by one.

    please paste errors.

    Also try using this query but change the ticket id

    ;WITH A
     AS (
    	SELECT a.TicketNumber ,b.IncidentID
    	FROM IncidentBase a
    	INNER JOIN incidentextensionbase b ON  a.IncidentID  = b.IncidentID
    	WHERE a.TicketNumber  ='cas-023'
    	)
    
    DELETE FROM IncidentBase
    WHERE TicketNumber IN (SELECT TicketNumber FROM A)
    		
    DELETE FROM incidentextensionbase
    WHERE IncidentID IN (SELECT IncidentID FROM A)
    


    Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Dia Agha .

    • Marked as answer by Charlie Liao Tuesday, December 30, 2014 2:09 AM
    Tuesday, December 23, 2014 4:21 PM
  • Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements (why do you think that “Table1” is a clear, precise name? No, it how we would name a physical tape drive in 1965). You should follow ISO-8601 rules for displaying temporal data. We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL. And you probably need to read and download the PDF for: 
    https://www.simple-talk.com/books/sql-books/119-sql-code-smells/

    >> I have two tables that has one relationship, that is the incident_id. <<

    NO. A relationship exists between entities. It is not an identifier. Have you ever read a book on RDBMS? You do not know any of the basic terms. Relationship are modeled in their town tables with REFERENCES to the tables that model the entities. 

    >> I need to delete records [sic] from IncidentBase and also from Incident_Extensions, the only column they have in common is the incident_id. <<

    Rows are not anything like records! Why do you think that the postfix “-base” is a valid data element name? Ever seen ISO-11179 rules or any book on data modeling? 

    >> The ticket number is in Incidents table but not in Incident_Extensions table, but Incident_Extensions table has some related information about the ticket numbers in Incidents table. <<


    Where is the DDL?? 

    >> I want the records [sic] to delete from Incidents and to also delete related information from Incident_Extensions. <<

    An SQL programmer would use a ON DELETE CASCADE clause in a REFERENCES constraint. 

    >> I tried this query: <<

    A deletion is a statement, not a query. And this is not valid ANSI/ISO Standard syntax; it comes from the original Sybase SQL Server and makes no sense in the standard model. 

    DELETE Incidents, Incident_Extensions
     FROM Incidents
     INNER JOIN 
     Incident_Extensions 
     ON Incidents.incident_id = Incident_Extensions.incident_id
     WHERE ticket_nbr IN ('cas-023', 'cas-028', 'cas-031');

    Here is a guess at DDL:

    CREATE TABLE Incidents
    (incident_id CHAR(10) NOT NULL PRIMARY KEY,
     …);

    CREATE TABLE Incident_Extensions
    (incident_id CHAR(10) NOT NULL 
       REFERENCES Incidents (incident_id)
       ON DELETE CASCADE,
     ticket_nbr CHAR(10) NOT NULL,
      PRIMARY KEY (incident_id, ticket_nbr),
     …);

    Since you did not have the manners to post DDL, I feel no obligation to explain this to you; go read a book and learn the terms and basic  principles before you post again. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Marked as answer by Charlie Liao Tuesday, December 30, 2014 2:08 AM
    Tuesday, December 23, 2014 6:36 PM

All replies

  • In a DELETE statement you always can delete data from one table, not from several.

    You have to delete first the data from table with foreign key, then from the primary key table.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, December 23, 2014 9:55 AM
  • ;WITH cte
    AS (
    	SELECT a.TicketNumber
    		,b.IncidentID
    	FROM IncidentBase a
    	INNER JOIN incidentextensionbase b ON a.IncidentID = b.IncidentID
    	WHERE a.TicketNumber IN (
    			'cas-023'
    			,'cas-028'
    			,'cas-031'
    			)
    	)
    --Select * from CTE -- To check list of Incident ID to be deleted
    DELETE FROM IncidentBase
    WHERE TicketNumber IN (SELECT TicketNumber FROM cte)
    		
    DELETE FROM incidentextensionbase
    WHERE IncidentID IN (SELECT IncidentID FROM cte)


    -Vaibhav Chaudhari

    • Proposed as answer by VSPatel Tuesday, December 23, 2014 10:03 AM
    Tuesday, December 23, 2014 10:00 AM
  • Read this article

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ba917d6d-9bae-4539-bec6-6274e8395780/how-do-i-delete-records-from-two-tables-with-only-one-relationship?forum=transactsql

    DELETE FROM t2
    FROM t1 AS t2

    what table does this truncate?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, December 23, 2014 10:53 AM
    Answerer
  • Thanks alot Vaibhav,

    The query gave an error here:

    DELETE FROM incidentextensionbase WHERE IncidentID IN (SELECT IncidentID FROM cte)

    It said the 'cte' is an invalid object! Please help

    Tuesday, December 23, 2014 2:51 PM
  • Can you try this?

    DELETE  from IncidentBase
    where IncidentID in(
    select IncidentID from IncidentBase
    INNER JOIN incidentextensionbase on IncidentBase.IncidentID = incidentextensionbase.IncidentID
    and TicketNumber IN ('cas-023', 'cas-028', 'cas-031'))
    --Prashanth

    Tuesday, December 23, 2014 3:05 PM
  • First select into a temp table records you want to delete.

    secondly don't try deleting all 3 ticket numbers in one go. do it one by one.

    please paste errors.

    Also try using this query but change the ticket id

    ;WITH A
     AS (
    	SELECT a.TicketNumber ,b.IncidentID
    	FROM IncidentBase a
    	INNER JOIN incidentextensionbase b ON  a.IncidentID  = b.IncidentID
    	WHERE a.TicketNumber  ='cas-023'
    	)
    
    DELETE FROM IncidentBase
    WHERE TicketNumber IN (SELECT TicketNumber FROM A)
    		
    DELETE FROM incidentextensionbase
    WHERE IncidentID IN (SELECT IncidentID FROM A)
    


    Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Dia Agha .

    • Marked as answer by Charlie Liao Tuesday, December 30, 2014 2:09 AM
    Tuesday, December 23, 2014 4:21 PM
  • Thanks alot Vaibhav,

    The query gave an error here:

    DELETE FROM incidentextensionbase WHERE IncidentID IN (SELECT IncidentID FROM cte)

    It said the 'cte' is an invalid object! Please help

    Please try below:

    	SELECT a.TicketNumber
    		,b.IncidentID
    	INTO #TempTable
    	FROM IncidentBase a
    	INNER JOIN incidentextensionbase b ON a.IncidentID = b.IncidentID
    	WHERE a.TicketNumber IN (
    			'cas-023'
    			,'cas-028'
    			,'cas-031'
    			)
    	
    --Select * from #TempTable -- To check list of Incident ID to be deleted
    DELETE FROM IncidentBase
    WHERE TicketNumber IN (SELECT TicketNumber FROM #TempTable)
    		
    DELETE FROM incidentextensionbase
    WHERE IncidentID IN (SELECT IncidentID FROM #TempTable)
    DROP TABLE #TempTable


    -Vaibhav Chaudhari


    Tuesday, December 23, 2014 4:31 PM
  • declare @basetable table (IncidentID int, col02 varchar(50))
    declare  @Extensiontable table (IncidentID int, col02 varchar(50))
    
    
    insert into @basetable values (1, 'India'), (2, 'China'),(3, 'USA')
    
    insert into @Extensiontable values (1, 'in Asia'), (2, 'in Asia'),(3, 'in North America')
    
    select * from @basetable b inner join @Extensiontable e on e.IncidentID=b.IncidentID
    
    
    ---if you want to delete records from base, you need to first delete it from extension table 
    
    ----thus 
    
    delete from @Extensiontable where IncidentID = 1
    delete from @basetable where IncidentID = 1


    ebro

    Tuesday, December 23, 2014 4:42 PM
  • Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements (why do you think that “Table1” is a clear, precise name? No, it how we would name a physical tape drive in 1965). You should follow ISO-8601 rules for displaying temporal data. We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL. And you probably need to read and download the PDF for: 
    https://www.simple-talk.com/books/sql-books/119-sql-code-smells/

    >> I have two tables that has one relationship, that is the incident_id. <<

    NO. A relationship exists between entities. It is not an identifier. Have you ever read a book on RDBMS? You do not know any of the basic terms. Relationship are modeled in their town tables with REFERENCES to the tables that model the entities. 

    >> I need to delete records [sic] from IncidentBase and also from Incident_Extensions, the only column they have in common is the incident_id. <<

    Rows are not anything like records! Why do you think that the postfix “-base” is a valid data element name? Ever seen ISO-11179 rules or any book on data modeling? 

    >> The ticket number is in Incidents table but not in Incident_Extensions table, but Incident_Extensions table has some related information about the ticket numbers in Incidents table. <<


    Where is the DDL?? 

    >> I want the records [sic] to delete from Incidents and to also delete related information from Incident_Extensions. <<

    An SQL programmer would use a ON DELETE CASCADE clause in a REFERENCES constraint. 

    >> I tried this query: <<

    A deletion is a statement, not a query. And this is not valid ANSI/ISO Standard syntax; it comes from the original Sybase SQL Server and makes no sense in the standard model. 

    DELETE Incidents, Incident_Extensions
     FROM Incidents
     INNER JOIN 
     Incident_Extensions 
     ON Incidents.incident_id = Incident_Extensions.incident_id
     WHERE ticket_nbr IN ('cas-023', 'cas-028', 'cas-031');

    Here is a guess at DDL:

    CREATE TABLE Incidents
    (incident_id CHAR(10) NOT NULL PRIMARY KEY,
     …);

    CREATE TABLE Incident_Extensions
    (incident_id CHAR(10) NOT NULL 
       REFERENCES Incidents (incident_id)
       ON DELETE CASCADE,
     ticket_nbr CHAR(10) NOT NULL,
      PRIMARY KEY (incident_id, ticket_nbr),
     …);

    Since you did not have the manners to post DDL, I feel no obligation to explain this to you; go read a book and learn the terms and basic  principles before you post again. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Marked as answer by Charlie Liao Tuesday, December 30, 2014 2:08 AM
    Tuesday, December 23, 2014 6:36 PM