Delete query simple - 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 2005 Forums

Transact-SQL (2005)

Delete query simple

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-09-04 : 05:29:47

[code]DECLARE @tempfun TABLE(col1 varchar(10))
INSERT INTO @tempfun SELECT
'aaa' UNION SELECT
'bbb' UNION SELECT
'ccc' UNION SELECT
'ddd'

DECLARE @tempfun2 TABLE(col1 varchar(10))
INSERT INTO @tempfun2 SELECT
'aaa' UNION SELECT
'bbb' UNION SELECT
'ccc' UNION SELECT
'ddd'

DECLARE @tempfun3 TABLE(col1 varchar(10))
INSERT INTO @tempfun3 SELECT
'aaa' UNION SELECT
'bbb' UNION SELECT
'ccc' UNION SELECT
'ddd'
select t1.col1, t2.col1, t3.col1
from @tempfun t1 join @tempfun2 t2 on t1.col1=t2.col1 join @tempfun t3 on t1.col1=t3.col1

DELETE FROM @tempfun t1 join @tempfun2 t2 on t1.col1=t2.col1 join @tempfun3 t3 on t1.col1 = t3.col1 WHERE t1.col1='aaa' and t2.col1='aaa' and t2.col1='aaa'

select t1.col1, t2.col1, t3.col1
from @tempfun t1 join @tempfun2 t2 on t1.col1=t2.col1 join @tempfun t3 on t1.col1=t3.col1[/code]
just wondering...is it possible to delete multiple table by 1 query?

Hope can help...but advise to wait pros with confirmation...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-04 : 05:37:58

Your code gives syntax error in delete statement

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-04 : 05:39:35

And by the way.
Your last select uses JOIN so it is clear that 'aaa' will not appear if delete has been succesful.

No, you're never too old to Yak'n'Roll if you're too young to die.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-09-04 : 05:48:50

i know my delete statement are wrong...thats the reason i asking...is it possible to have a delete statement for multiple table

Hope can help...but advise to wait pros with confirmation...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-04 : 05:53:18

No it isn't.
You cannot shoot 3 ducks with 1 bullet

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-04 : 05:54:39

Have you read about on delete cascade?

Madhivanan

Failing to plan is Planning to fail

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-09-04 : 05:57:30

quote:
Originally posted by webfred

No it isn't.
You cannot shoot 3 ducks with 1 bullet

No, you're never too old to Yak'n'Roll if you're too young to die.

i tot i gonna have a curving bullet doh..

Hope can help...but advise to wait pros with confirmation...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-09-04 : 05:58:27

quote:
Originally posted by madhivanan

Have you read about on delete cascade?

Madhivanan

Failing to plan is Planning to fail

not using cascade option...

Hope can help...but advise to wait pros with confirmation...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-04 : 06:03:37

quote:
Originally posted by waterduck

quote:
Originally posted by madhivanan

Have you read about on delete cascade?

Madhivanan

Failing to plan is Planning to fail

not using cascade option...

Hope can help...but advise to wait pros with confirmation...

Why do you want to do this?

Madhivanan

Failing to plan is Planning to fail

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-09-04 : 06:15:44

[code]string sql = "DELETE table1 WHERE ID = " + Session["name"].ToString();
SqlCommand cmd= new SqlCommand(sql, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();[/code]
i trying to eliminate these code into 1....
ps. no sto-pro plz

Hope can help...but advise to wait pros with confirmation...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-04 : 06:18:03

Try this

string sql = "DELETE table1 WHERE ID = " + Session["name"].ToString();DELETE from othertable where ;"
SqlCommand cmd= new SqlCommand(sql, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();

Madhivanan

Failing to plan is Planning to fail

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-09-04 : 06:20:57

argh...madhi u smart ^^...just concat it

so there is no way to combine table to delete?just curious

Hope can help...but advise to wait pros with confirmation...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-04 : 06:23:33

quote:
Originally posted by waterduck

argh...madhi u smart ^^...just concat it
so there is no way to combine table to delete?just curious

Hope can help...but advise to wait pros with confirmation...

I think it is not possible the way you wanted
Why dont you use my previous suggestion?

Madhivanan

Failing to plan is Planning to fail

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-09-04 : 06:26:22

quote:
Originally posted by madhivanan

quote:
Originally posted by waterduck

argh...madhi u smart ^^...just concat it
so there is no way to combine table to delete?just curious

Hope can help...but advise to wait pros with confirmation...

I think it is not possible the way you wanted
Why dont you use my previous suggestion?

Madhivanan

Failing to plan is Planning to fail

yea im using that...now i see all insert, update and delete will not work for multiple table

Hope can help...but advise to wait pros with confirmation...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources