Delete query for two tables - 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 for two tables

Author

Topic

vignesht50
Yak Posting Veteran

82 Posts

Posted - 2013-11-26 : 09:43:41

Hi All,

I have two tables PROFILES & ROLE

CREATE TABLE PROFILES(
ID varchar(20) UNIQUE NOT NULL,
Name varchar(40) NULL,
Address varchar(25) NULL
)

insert into PROFILES values ('rryan','Jamie Fox','jfox@live.com')
insert into PROFILES values ('mclark','Michael Clark','mclark@live.com')
insert into PROFILES values ('djones','Dean Jones','djones@live.com')
insert into PROFILES values ('jfox','Jamie Fox','jfox@live.com')
insert into PROFILES values ('drivers','Doc Rivers','drivers@live.com')

CREATE TABLE ROLE(
ID varchar(20) UNIQUE NOT NULL,
Role char(10) NOT NULL,
Applications char (10) NOT NULL
)

insert into ROLE values ('rryan','M','Consultant')
insert into ROLE values ('mclark','AM','Organizer')
insert into ROLE values ('djones','SM','Admin')
insert into ROLE values ('jfox','M','Consultant')
insert into ROLE values ('drivers','AM','Organizer')

This query joins both the tables and gets displayed in a grid.
select P.ID, Name, Address, Role, Applications
from PROFILES P
Inner Join ROLE R
ON P.ID= R.ID

I need to write a delete query which deletes data from both the table in a single query.
I am bit confused as the action has to happen for two tables. Can anyone help me on this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-26 : 09:54:23

[code]
declare @DELETED_IDs table
(
ID varchar(20)
)

DELETE t
OUTPUT DELETED.ID INTo @DELETED_IDs
FROM PROFILES t
WHERE ... your any conditions here

DELETE t
FROM ROLES t
INNER JOIN @DELETED_IDs d
ON d.ID = t.ID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

vignesht50
Yak Posting Veteran

82 Posts

Posted - 2013-11-26 : 10:06:03

quote:
Originally posted by visakh16

declare @DELETED_IDs table
(
ID varchar(20)
)

DELETE t
OUTPUT DELETED.ID INTo @DELETED_IDs
FROM PROFILES t
WHERE ... your any conditions here

DELETE t
FROM ROLES t
INNER JOIN @DELETED_IDs d
ON d.ID = t.ID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Hi Visakh,
I am using this query for asp.net. So is this fine

DELETE P.*,R.*
FROM PROFILES P
LEFT JOIN ROLE R ON P.ID=R.ID
WHERE P.ID=' '

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-27 : 04:25:00

quote:
Originally posted by vignesht50

quote:
Originally posted by visakh16

declare @DELETED_IDs table
(
ID varchar(20)
)

DELETE t
OUTPUT DELETED.ID INTo @DELETED_IDs
FROM PROFILES t
WHERE ... your any conditions here

DELETE t
FROM ROLES t
INNER JOIN @DELETED_IDs d
ON d.ID = t.ID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Hi Visakh,
I am using this query for asp.net. So is this fine

DELETE P.*,R.*
FROM PROFILES P
LEFT JOIN ROLE R ON P.ID=R.ID
WHERE P.ID=' '

nope
you cant delete multiple tables records using single DELETE Statement
So you need to use it as I suggested

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources