Delete from temp table

1

Hi Peeps,

I have a query as below:

if exists (select * from sys.objects where name = '#Temp' and type = 'u')
drop table #Temp

CREATE TABLE #Temp
(
Id int
, Type text
, status text
)
INSERT INTO #Temp
(
Id
, Type
, status
)

SELECT  
			c.Comm_CommunicationId
		  , c.Comm_Type
		  , c.comm_status
  FROM communication c
  LEFT
  JOIN Comm_Link cl
    ON cl.CmLi_Comm_CommunicationId   = c.Comm_CommunicationId
  LEFT
  JOIN Recurrence r
    ON r.Recu_RecurrenceId			  = c.Comm_RecurrenceId
  LEFT 
  JOIN Escalations e
    ON e.Escl_RecordID				  = cl.CmLi_Comm_CommunicationId
   AND e.Escl_TableId				  = 29
  LEFT
  JOIN Library l
    ON l.libr_communicationId		  = c.Comm_CommunicationId
 WHERE c.Comm_UpdatedDate             <= '2016-01-01'

SELECT
*
FROM #Temp

I want to delete the records from all the above tables mentioned in the select query - can you please advise how to achieve it?

Thanks

2

Make sure the #TEMP table includes the PKey column(s) from all the tables (or some other, unique, reference to each row in each of the tables)

You could, perhaps, just make sure that the PKey columns for [Comm_Link] are included, and delete all associated table records based on that, but if a new record is added to one of the tables associated with a [Comm_Link] record in your report, that will ALSO be deleted (which may, or may not!, be what you want!)

DELETE L
FROM #TEMP AS T
    JOIN Library as L
       ON L.PKey1 = T.LibraryPKey1
      AND L.PKey2 = T.LibraryPKey2
      ...

repeat for other tables.

Using the "just [Comm_Link] PKeys approach" then

DELETE L
  FROM #TEMP AS T
    JOIN communication c
        ON C.PKey1 = T.CommsPKey1
      AND C.PKey2 = T.CommsPKey2
      ...
  LEFT
  JOIN Comm_Link cl
    ON cl.CmLi_Comm_CommunicationId   = c.Comm_CommunicationId
  LEFT
  JOIN Recurrence r
    ON r.Recu_RecurrenceId			  = c.Comm_RecurrenceId
  LEFT 
  JOIN Escalations e
    ON e.Escl_RecordID				  = cl.CmLi_Comm_CommunicationId
   AND e.Escl_TableId				  = 29
  LEFT
  JOIN Library l
    ON l.libr_communicationId		  = c.Comm_CommunicationId

DELETE E
  FROM #TEMP AS T
    JOIN communication c
        ON C.PKey1 = T.CommsPKey1
      AND C.PKey2 = T.CommsPKey2
      ...
  LEFT
  JOIN Comm_Link cl
    ON cl.CmLi_Comm_CommunicationId   = c.Comm_CommunicationId
  LEFT
  JOIN Recurrence r
    ON r.Recu_RecurrenceId			  = c.Comm_RecurrenceId
  LEFT 
  JOIN Escalations e
    ON e.Escl_RecordID				  = cl.CmLi_Comm_CommunicationId
   AND e.Escl_TableId				  = 29

DELETE R
  FROM #TEMP AS T
    JOIN communication c
        ON C.PKey1 = T.CommsPKey1
      AND C.PKey2 = T.CommsPKey2
      ...
  LEFT
  JOIN Comm_Link cl
    ON cl.CmLi_Comm_CommunicationId   = c.Comm_CommunicationId
  LEFT
  JOIN Recurrence r
    ON r.Recu_RecurrenceId			  = c.Comm_RecurrenceId

DELETE CL
  FROM #TEMP AS T
    JOIN communication c
        ON C.PKey1 = T.CommsPKey1
      AND C.PKey2 = T.CommsPKey2
      ...
  LEFT
  JOIN Comm_Link cl
    ON cl.CmLi_Comm_CommunicationId   = c.Comm_CommunicationId

DELETE C
  FROM #TEMP AS T
    JOIN communication c
        ON C.PKey1 = T.CommsPKey1
      AND C.PKey2 = T.CommsPKey2
      ...```