Delete from two table

Question

I want to delete data from two tables at a time.

Example:

Table 1:

create table del1
(
cola varchar(10) primary key,
colb varchar(10)
);

Table 2:

create table del2
(
cola varchar(10) foreign key references del1(cola) on delete cascade,
colb varchar(10)
)

Inserting some data:

insert into del1 values('a','b');
insert into del2 values('a','d');

Query to delete the records:

delete from del1 as d1
inner join del2 as d2
on d1.cola= d2.cola 
where d1.cola= 'a'

Error:

Incorrect syntax near the keyword 'as'.

I'm guessing the `on` condition was meant to reference `d2` there. Is the actual delete condition in the `where` clause actually based on `cola` (`colb` is irrelevant, and so it could also, as easily, reference `d2` instead of `d1`)? — Damien_The_Unbeliever, Oct 29 '14 at 07:40
[Why can't I use an alias in a DELETE statement?](http://stackoverflow.com/questions/11005209/why-cant-i-use-an-alias-in-a-delete-statement) — bummi, Oct 29 '14 at 07:42

Pரதீப் · Accepted Answer · 2014-10-29T08:44:13.900

3

Actually u dont need to join the two table's. delete on del1 table it will automatically delete the del2 table since u have on delete cascade

This is what you need.

delete from del1 where cola='a'

This statement will delete from del1 where cola='a' and also the del2 table where cola='a'

edited Oct 29 '14 at 08:44

answered Oct 29 '14 at 08:01

Pரதீப்

91,748
19
131
172

Dgan · Answer 2 · 2014-10-29T08:07:10.087

2

Join is Not needed. Due to On Cascade delete it will delete both records

delete from del1 where  cola= 'a'

edited Oct 29 '14 at 08:07

answered Oct 29 '14 at 07:42

Dgan

10,077
1
29
51

Delete from two table

2 Answers2