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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -