help me DELETE PROBLEM!! - 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 2000 Forums

Transact-SQL (2000)

help me DELETE PROBLEM!!

Author

Topic

princess82
Starting Member

10 Posts

Posted - 2006-07-15 : 23:14:00

i have this table name username and list of data
ex:
username
NAMA<COLUMN NAME>
HANA
HANA
HANA
HANA

what is the syntax to dlete only the three hana lefting only one hana in a column.

should i use DELETE or DISTINCT... or is there any method

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-07-16 : 00:43:33

Here's what I would do. Select one value into a separate table:

SELECT TOP 1 Name INTO #tmp_Name FROM username WHERE Name = 'HANA'

Then I would delete all the values of 'HANA', then insert the single value of 'HANA' back into username from the temp table.

princess82
Starting Member

10 Posts

Posted - 2006-07-16 : 00:52:51

let say if there is other name like

mina
jack
hana
jim
klis
hana
jys
kal
henry
hana

and we want only the first hana to be selected,what should we do?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-16 : 06:47:46

What is the primary key of this table ?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-07-16 : 06:56:17

Somthing like this


Declare @var Table 
(
tmp varchar(10)
)

Insert @var 
Select 'mina' Union All 
Select 'jack' Union All 
Select 'hana' Union All 
Select 'jim' Union All 
Select 'klis' Union All 
Select 'hana' Union All 
Select 'jys' Union All 
Select 'kal' Union All 
Select 'henry' Union All 
Select 'hana'

Select Distinct Tmp Into #Temp From @var

Delete @var

Insert @var
Select * from #Temp 

Select * From @var

Drop TAble #Temp

Chirag

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-07-16 : 07:42:20

IF you have primary key in your Table then you can use the following
Query..


Declare @var Table 
(
[ID] int identity(1,1),
tmp varchar(10)
)

Insert @var 
Select 'mina' Union All 
Select 'jack' Union All 
Select 'hana' Union All 
Select 'jim' Union All 
Select 'klis' Union All 
Select 'hana' Union All 
Select 'jys' Union All 
Select 'kal' Union All 
Select 'henry' Union All 
Select 'hana' Union All 
Select 'henry' 

--Select * From @var

Delete From @var Where ID in (
Select v1.ID From  @var v1 Left join 
(Select Min([ID])as id ,Tmp From @var v1 Where Tmp in (Select Tmp From @var v 
where v1.[id] <> v.[id] and v1.[tmp] = v.[tmp]) Group by Tmp) as f 
on v1.[ID] = f.[ID]
Where f.ID is Not null)


Select * From @var

Chirag

allan_houston
Starting Member

18 Posts

Posted - 2006-07-16 : 17:20:43

to delete only 3 hanas:

Set Rowcount 3
Delete
From [table]

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-16 : 20:40:44

There's no method to do this easily as what you have is not a table in a relational database so can't be handled by relational operations.
To get rid of all duplicates in small tables and allow a unique index to be added:

set rowcount 1
select 1
while @@rowcount > 0
delete tbl
where name in (select name from tbl group by name having count(*) > 1)
set rowcount 0

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-17 : 07:18:26

Also not pretty, but here's an alternative (temporarily add an identity column, use it, and drop it)...

--data
if object_id('tempdb..#t') is not null drop table #t
create table #t (name varchar(10))

insert #t
select 'mina' union all 
select 'jack' union all 
select 'hana' union all 
select 'jim' union all 
select 'klis' union all 
select 'hana' union all 
select 'jys' union all 
select 'kal' union all 
select 'henry' union all 
select 'hana'

--calculation
alter table #t add id int identity(1, 1)
go

delete a from #t a where exists (select * from #t where name = a.name and id < a.id)

alter table #t drop column id
go

select * from #t

/*results
name       
---------- 
mina
jack
hana
jim
klis
jys
kal
henry
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources