'DELETE' Query problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

'DELETE' Query problem

Author

Topic

pooja28d
Starting Member

8 Posts

Posted - 2011-03-04 : 21:34:55

Hi to all, I created the following two table

create table mail(mid integer primary key, name varchar(20) )
create table phone( pid integer foreign key references mail(email), phone integer)
'delete record of pid=12'. that means, i have to delete record of pid=12 from oth table. so, i ran following query.

delete from mail, phone on mail.mid=phone.pid where pid=12
but i got the error like 'check the syntax near from', what to do then to get the proper output.

Thanks & regards, Pooja.

sql foreign-keys

Thanks & regards,
Pooja.

Sachin.Nand

2937 Posts

Posted - 2011-03-04 : 23:33:05

SQL Server does not support deletion from multiple tables.You can use the Output deleted clause to delete from multiple tables if you are using SQL 2005 or higher.


declare @deletedphone table(pid int)

declare @mail table(mid int)
insert @mail
select 1 union
select 2 union
select 4

declare @phone table(pid int)
insert @phone
select 1 union
select 2 

delete from  @phone output deleted.pid into @deletedphone


delete t from
(
   select t.mid from @mail t where exists(select 1 from @deletedphone d where d.pid=t.mid)
  
)t

select * from @mail

PBUH

pooja28d
Starting Member

8 Posts

Posted - 2011-03-05 : 00:15:03

@sachin, your code is look somewhat difficult. I want teach this example to student, so is it there another simple query which deletes record from both table at a same time?

Thanks & regards,
Pooja.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-05 : 01:35:12

if cascade delete option is enabled on the relation then simply Deleting the record from the Master/Primary Table will remove it from the child/Foreign key table as well e.g.

Delete from Mail where PID=12

Or if this option is not enabled use two transaction

Delete from phone where PID=12
Go
Delete from Mail where PID=12

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources