How can I delete from a table using the join and where clause?

Question

The following is my code so far and the select query is what I would like to delete from the Events table.

declare @maxsnapevent table (sita varchar(10), snap date)
insert into @maxsnapevent
select sita, max(snapshot_date) from ukrmc.dbo.strategy group by sita  
--select * from @maxsnapevent 


--need to delete everything that the following code gives  
select events.sita, events.date, events.event from ukrmc.dbo.events events
join @maxsnapevent max on max.sita = events.sita  
where date >= max.snap and events.sita != 'lcypd' and events.sita != 'lonza'

example data from the strategy table:

date          sita    Event              Snapshot_date
2018-01-01   London   Bank Holiday       2017-12-31 
2018-01-02   London                      2017-12-31 
2018-01-03   London                      2017-12-31
2018-01-04   London   Concert            2017-12-31
2018-01-02   London                      2018-01-01
2018-01-03   London                      2018-01-01
2018-01-04   London   Concert            2018-01-01
2018-01-01   Bham   Bank Holiday         2017-12-31 
2018-01-02   Bham                        2017-12-31 
2018-01-03   Bham                        2017-12-31
2018-01-04   Bham                        2017-12-31
2018-01-02   Bham                        2018-01-01
2018-01-03   Bham     Charity            2018-01-01
2018-01-04   Bham                        2018-01-01

example data from the events table:

date         sita    Event
2018-01-01   London  Bank Holiday
2018-01-02   London  
2018-01-03   London  
2018-01-04   London  Concert
2018-01-01   Bham    Bank Holiday
2018-01-02   Bham  
2018-01-03   Bham  
2018-01-04   Bham    Concert

As you can see each snapshot has several sitas with several dates.

That's described in the docs, in the [Using joins and subqueries to data in one table to delete rows in another table](https://learn.microsoft.com/en-us/sql/t-sql/statements/delete-transact-sql?view=sql-server-2017#d-using-joins-and-subqueries-to-data-in-one-table-to-delete-rows-in-another-table) section: `DELETE SomeTable FROM SomeTable Inner Join Other table...` — Panagiotis Kanavos, Oct 03 '18 at 14:15

Simon Restrepo · Accepted Answer · 2018-10-03T14:35:22.680

2

Have you tried this next code?

delete events
from ukrmc.dbo.events events
join @maxsnapevent max on max.sita = events.sita  
where date >= max.snap and events.sita != 'lcypd' and events.sita != 'lonza'

edited Oct 03 '18 at 14:35

answered Oct 03 '18 at 14:14

Simon Restrepo

313
1
2
15

Should the first line be delete sita, date, event ?? – Sorath Oct 03 '18 at 14:27
@Sorath no, you don't delete columns, you delete rows. – Sean Lange Oct 03 '18 at 14:28
@Sorath No, the first line indicates what table you want to perform the delete. You Delete ROWS, not columns. I suggest you read more about the DML actions in SQL Server. Microsoft has an incredible documentation for this. – Simon Restrepo Oct 03 '18 at 14:39
Thanks a lot for your help guys! – Sorath Oct 03 '18 at 15:02

How can I delete from a table using the join and where clause?

1 Answers1