Delete statement - 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 statement

Author

Topic

cardgunner

326 Posts

Posted - 2013-09-18 : 10:14:46

Trying to figure out how to write a delete statement where 2 variables are met in(thru) a select statement.

In the example below I want to delete the record where t_itbp and t_seqn is in the select statement. I'm sure my terminolgy is not right.


delete from ttcext001100 
where t_itbp and t_seqn in 
(select cext001.t_itbp,
		cext001.t_seqn
from	ttcext001100 cext001
join 
(select *
from
(select count(t_itbp) cnt, 
		t_itbp, 
		sum(t_conl)t_conl,
		sum(t_aval)t_aval
from	ttcext001100
group by t_itbp
) tmp
where cnt>1 and t_aval=0 and t_conl=0
) tmp2 on tmp2.t_itbp=cext001.t_itbp
where cext001.t_itbp=35005 and cext001.t_seqn=1
);

My goal with this is once it works is to take this where clause "where cext001.t_itbp=35005 and cext001.t_seqn=1" out. It's just there as a safety.

CardGunner

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-18 : 10:25:40

[code]
DELETE w
FROM (
SELECT t_itbp,
t_seqn,
COUNT(*) OVER (PARTITION BY t_itbp) AS theCount,
SUM(t_conl) OVER (PARTITION BY t_itbp) AS theSum1,
SUM(t_aval) OVER (PARTITION BY t_itbp) AS theSum2
FROM dbo.ttcext001100
) AS w
WHERE theCount > 1
AND theSum1 = 0
AND theSum2 = 0
AND t_seqn = 1
AND t_itbp = 35005;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

cardgunner

326 Posts

Posted - 2013-09-18 : 11:02:48

Thanks. A question about OVER (PARTITION BY t_itbp). That works the same as the group by clause, right? Any reason for not using the group by clause?

CardGunner

cardgunner

326 Posts

Posted - 2013-09-18 : 11:05:50

Correction. Tried the Group by and it doesn't work. I'll have to read up on this and when I can use it.

CardGunner

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-18 : 11:06:39

It's a windowed function. It returns the same value as n GROUP BY, but "per-row" basis.
That's why you can delete directly from the result.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-18 : 11:06:44