•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 402,510 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,689 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MS SQL advertiser: Programming Forums
Views: 1297 | Replies: 13
 |
•
•
Join Date: Jan 2008
Location: istanbul
Posts: 265
Reputation: 
Rep Power: 1
Solved Threads: 23
Posting Whiz in Training
your final sql will be as follows :
this code below deletes only first found row of row sets having the same value for the column nvarchar1 and the value of 1 for the column int1
declare @varchar1 nvarchar(50)
declare mycursor cursor for
select varchar1,
from mytable
group by varchar1
having count(*) > 1
open mycursor
FETCH NEXT FROM mycursor INTO @varchar1
WHILE @@FETCH_STATUS = 0
begin
delete top (1) from mytable where (@varchar1=varchar1 and int1=1)
FETCH NEXT FROM mycursor INTO @varchar1
end
close mycursor
deallocate mycursor
this code below deletes only first found row of row sets having the same value for the column nvarchar1 and the value of 1 for the column int1
declare @varchar1 nvarchar(50)
declare mycursor cursor for
select varchar1,
from mytable
group by varchar1
having count(*) > 1
open mycursor
FETCH NEXT FROM mycursor INTO @varchar1
WHILE @@FETCH_STATUS = 0
begin
delete top (1) from mytable where (@varchar1=varchar1 and int1=1)
FETCH NEXT FROM mycursor INTO @varchar1
end
close mycursor
deallocate mycursor
Serkan Şendur
MCAD.NET
MCAD.NET
•
•
Join Date: Jan 2008
Posts: 5
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
•
•
•
•
Originally Posted by serkansendur 
your final sql will be as follows :
this code below deletes only first found row of row sets having the same value for the column nvarchar1 and the value of 1 for the column int1
declare @varchar1 nvarchar(50)
declare mycursor cursor for
select varchar1,
from mytable
group by varchar1
having count(*) > 1
open mycursor
FETCH NEXT FROM mycursor INTO @varchar1
WHILE @@FETCH_STATUS = 0
begin
delete top (1) from mytable where (@varchar1=varchar1 and int1=1)
FETCH NEXT FROM mycursor INTO @varchar1
end
close mycursor
deallocate mycursor
I'm sorry Serkan but i don't know where to open mycursor! all i have is SQL enterprise manager, in there i open the table that i want, then click on sql to write my query. I haven't seen any SQL statement that has begin, end.... as i said i'm not expert by any means
.Thanks man.
•
•
Join Date: Jan 2008
Location: istanbul
Posts: 265
Reputation:
Rep Power: 1
Solved Threads: 23
Posting Whiz in Training
why dont you just copy paste the code i wrote for you? switch to sql view then paste the given code, then refresh your table, you will see the targeted records are gone.
Serkan Şendur
MCAD.NET
MCAD.NET
•
•
Join Date: May 2007
Posts: 8
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
Re: help with an sql statement Here you go. I've changed the table structure to match your table
You should be able to copy the section that I've marked out and change the name of the table (in both positions) and it will work.
All the best,
Zadjil
You should be able to copy the section that I've marked out and change the name of the table (in both positions) and it will work.
create table #YourTableName (UniqueInt INT, Date1 DATETIME, int1 INT, Date2 DATETIME, varchar1 VARCHAR(50) ) Insert into #YourTableName Values (1, '2008-1-31 12:00:00', 10, '2008-1-31 12:00:00', 'a') Insert into #YourTableName Values (2, '2008-1-31 12:00:00', 10, '2008-1-31 12:00:30', 'a') Insert into #YourTableName Values (3, '2008-1-31 12:00:00', 11, '2008-1-31 12:00:00', 'a') Insert into #YourTableName Values (4, '2008-1-31 12:00:00', 10, '2008-1-31 12:02:00', 'a') Select * from #YourTableName /* Copy from here to below - rename the #YourTableName to your own name */ DELETE FROM later --Delete the later result FROM --Join the table to itself where the values match #YourTableName later INNER JOIN #YourTableName ealier ON later.int1 = ealier.int1 and later.varchar1 = ealier.varchar1 WHERE -- make sure the ones in the ealier table are before the ones in the later table (stops the rows matching themselves) ealier.Date2 < later.Date2 AND -- make sure the later date is less(or equal) than one minute from the earlier date later.Date2 <= DATEADD(minute,1, ealier.Date2) /* End copy */ Select * from #YourTableName Drop table #YourTableName
All the best,
Zadjil
|
•
•
•
•
•
•
•
•
DaniWeb MS SQL Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
•
•
•
•
ajax asp database decimal seperator thousand seperator when using sql server deleting records from ms sql table where columns have duplicate values developer development hacker management studio 2005 microsoft msdn news office security software sql sql cache dependency with polling-based invalidation survey vista
- is there something wrong with my SQL statement? (PHP)
- Syntax error in "like" in sql statement (ASP.NET)
- Question about SQL statement (MS SQL)
- SQL statement error (ASP.NET)
- sql statement (Visual Basic 4 / 5 / 6)
- run sql statement in asp (ASP)
Other Threads in the MS SQL Forum
- Previous Thread: Complex Store Procedure
- Next Thread: questions in views and some others
