Delete a field from a table - 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

General SQL Server Forums

New to SQL Server Programming

Delete a field from a table

Author

Topic

maverick0681
Starting Member

2 Posts

Posted - 2009-09-02 : 01:54:57

Hello all, I have a table with enteries

1
2
3
1
5

Now i just want to remove the first '1' i mean my result should be
2
3
1
5
What should I do?

shaggy
Posting Yak Master

248 Posts

Posted - 2009-09-02 : 02:16:32

If it is sql 2005
use
delete top(1) from tablename

shaggy
Posting Yak Master

248 Posts

Posted - 2009-09-02 : 02:17:50

if it is < 2005

delete a from (select top(1) * from tablename order by columnname) a

chriztoph
Posting Yak Master

184 Posts

Posted - 2009-09-02 : 02:36:40

[code]BEGIN
DELETE N
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY <Some Fields Here> ORDER BY <Some Fields Here>) AS <Alias>
FROM <Your Tbale Here>
) AS N
WHERE Customer_ID > 1
END
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-02 : 06:01:41

quote:

Originally posted by chriztoph

BEGIN
	DELETE N
	FROM	
	(
	SELECT	ROW_NUMBER() OVER (PARTITION BY <Some Fields Here> ORDER BY <Some Fields Here>) AS <Alias>
	FROM	<Your Tbale Here>
	) AS N
	WHERE	Customer_ID > 1
	END

whats the necessity of partition by here?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-02 : 06:05:19

quote:
Originally posted by shaggy

If it is sql 2005
use
delete top(1) from tablename

Top without order by is meaningless

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-02 : 06:09:05

If the version is 2005 or more


declare @t table(i int)
insert into @t
select 1 union all select 2 union all select 3 union all select 1 
union all select 5
select i from @t 

delete t from 
(
	select ROW_NUMBER() over (order by (select 0)) as n from @t
) as t
where n=1

select i from @t

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources