Delete from multiple 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

SQL Server 2008 Forums

Transact-SQL (2008)

Delete from multiple tables

Author

Topic

kwacz23
Starting Member

44 Posts

Posted - 2013-11-05 : 03:58:57

Hi

Can you help me with below query? I would like to delete records from 3 tables in one query

i am trying below:

DELETE dbo.ExtAttribute , dbo.ExtAttributeValue FROM dbo.ExtAttributeProduct
INNER JOIN dbo.ExtAttribute ON dbo.ExtAttributeProduct.AttributeId = dbo.ExtAttribute.Id
INNER JOIN dbo.ExtAttributeValue ON dbo.ExtAttributeValue.AttributeId = dbo.ExtAttributeProduct.AttributeId
WHERE ProductId = 'GPTEST_NEW'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-05 : 04:22:17

you cant. you need to write separate DELETE statements for that

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

suryayadav
Starting Member

4 Posts

Posted - 2013-11-07 : 06:56:54

yes u can
heres the code

delete from <table1>,<table2>,<table3>,<table4>

surya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-07 : 07:21:17

quote:
Originally posted by suryayadav

yes u can
heres the code

delete from <table1>,<table2>,<table3>,<table4>

surya

where did you get this from?
did you test it for any actual tables?
try below code and see what you get


CREATE TABLE #t1
(
ID int,
Val varchar(5)
)

CREATE TABLE #t2
(
ID int,
Val varchar(5)
)

INSERT #t1
VALUES(1,'xxx'),(2,'ewfe')

INSERT #t2
VALUES(1,'xxx'),(2,'ewfe'),(3,'klhiohj')

DELETE FROm #t1,#t2

probably you meant drop instead of DELETE

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

sgondesi
Posting Yak Master

200 Posts

Posted - 2013-11-08 : 04:18:57

quote:
Originally posted by visakh16

quote:
Originally posted by suryayadav

yes u can
heres the code

delete from <table1>,<table2>,<table3>,<table4>

surya

where did you get this from?
did you test it for any actual tables?
try below code and see what you get

CREATE TABLE #t1
(
ID int,
Val varchar(5)
)

CREATE TABLE #t2
(
ID int,
Val varchar(5)
)

INSERT #t1
VALUES(1,'xxx'),(2,'ewfe')

INSERT #t2
VALUES(1,'xxx'),(2,'ewfe'),(3,'klhiohj')

DELETE FROm #t1,#t2

probably you meant drop instead of DELETE

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

sir,
i am getting below mentioned error when i tried to execute the code posted by you.

Msg 102, Level 15, State 1, Line 23
Incorrect syntax near ','.

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-08 : 04:46:00

quote:
Originally posted by sgondesi

quote:
Originally posted by visakh16

quote:
Originally posted by suryayadav

yes u can
heres the code

delete from <table1>,<table2>,<table3>,<table4>

surya

where did you get this from?
did you test it for any actual tables?
try below code and see what you get

CREATE TABLE #t1
(
ID int,
Val varchar(5)
)

CREATE TABLE #t2
(
ID int,
Val varchar(5)
)

INSERT #t1
VALUES(1,'xxx'),(2,'ewfe')

INSERT #t2
VALUES(1,'xxx'),(2,'ewfe'),(3,'klhiohj')

DELETE FROm #t1,#t2

probably you meant drop instead of DELETE

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

sir,
i am getting below mentioned error when i tried to execute the code posted by you.

Msg 102, Level 15, State 1, Line 23
Incorrect syntax near ','.

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.

Exactly
That was my point

you CANT delete data from multiple tables using single DELETE

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

sgondesi
Posting Yak Master

200 Posts

Posted - 2013-11-08 : 05:17:33

Ok sir.
thank you.

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources