CODEWITHSUNDEEP
sql-servert-sql
user3449922
user3449922

Reputation:

T-SQL : delete from select

I have a situation like this:

table_Associazione

ID     Cod     Descr
--------------------
001    CAR1    Fiat
002    CAR1    Fiat
003    CAR1    Fiat
004    CAR1    Fiat
005    CAR2    Opel

table_cars

ID     Name    Descr
--------------------
001    Tipo    4 ruote
002    Panda   4 ruote
003    Alfa    4 ruote
004    Beta    4 ruote
005    tera    3 ruote

I need to delete all records (in this case 001-004) from the table table_cars where table_Associazione.Cod = CAR1.

I think this is what I need:

delete from 
(select * 
 from td_cars 
 inner join tb_Associazione on tb_Associazione.ID = td_cars.ID 
 where tb_Associazione.Cod = 'CAR1')

I ask if theoretically this is the correct way and if no please suggest what is the best way to do it.

Thanks !

Upvotes: 0

Views: 2255

Answers (2)

GregS_SDBA
GregS_SDBA

Reputation: 130

delete * from table_cars a
    inner join table_Associazione b 
       on b.id = a.id   
where a.cod='CAR1'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269483

One method is a where clause:

delete c from td_cars c
    where exists (select 1
                  from tb_Associazione a 
                  where a.ID = c.ID and a.Cod = 'CAR1'
                 );

Upvotes: 2

Related Questions