August 28, 2009 at 12:40 pm
Hello SQLServerCentral gurus,
I need to delete all sales for custid within specific date range. As of now I am using a cursor (bad think). I was thinking of using a while loop instead of the cursor, but not sure if this will be better. Here is my code:
***
create table #delcust(custid varchar(6), mindate int, maxdate int)
-- for every custid in load_sales_cust table, find the mun & max date.
insert into #delcust(custid, mindate, maxdate)
select custid, min(ddate), max(ddate) from load_sales_cust
group by custid
declare @custid varchar(6)
declare @mindate int
declare @maxdate int
declare datecursor cursor for
select custid, mindate, maxdate from ##delcust
open datecursor
while @@FETCH_STATUS = 0
BEGIN
delete from dw_sales_cust
where icustid = @custid
and dtdate >=dbo.convertIntDateToDatetime(@mindate)
and dtdate <= dbo.convertIntDateToDatetime(@maxdate)
fetch next from datecursor into @custid, @mindate, @maxdate
END
CLOSE DATECURSOR
DEALLOCATE DATECURSOR
***
Does someone has a good idea to improve this code? I did some test and the while/loop is not that faster.
Thanks,
August 28, 2009 at 12:44 pm
unless I'm missing something, you do not need to use an iteration to delete your target rows.
simply use a join in the FROM clause in your delete statement.
there are good examples here:
http://msdn.microsoft.com/en-us/library/ms189835%28SQL.90%29.aspx
~BOT
Craig Outcalt
August 28, 2009 at 1:04 pm
Thanks BOT,
How can I join multiple columns form only 2 tables?
Sorry for my stupid questions
August 28, 2009 at 2:14 pm
If I understand your question you want to know how to join a table on 2 columns?
let's do an example:
table1
column a
column b
table2
column a
column c
delete table1
from table1 t1 inner join table2 t2
on t1.a=t2.a and t1.b = t2.c --<--here is where the magic is
where t2.c = 100
Craig Outcalt
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply