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
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
...```