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

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 section: DELETE SomeTable FROM SomeTable Inner Join Other table... — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Oct 3, 2018 at 14:15

Simon Restrepo · Accepted Answer · 2018-10-03 14:35:22Z

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 3, 2018 at 14:35

answered Oct 3, 2018 at 14:14

Simon Restrepo

3431 gold badge2 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sorath Over a year ago

Should the first line be delete sita, date, event ??

Sean Lange Over a year ago

@Sorath no, you don't delete columns, you delete rows.

Simon Restrepo Over a year ago

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

Sorath Over a year ago

Thanks a lot for your help guys!

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related