Deleting row from 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

Deleting row from table

Author

Topic

velvettiger
Posting Yak Master

115 Posts

Posted - 2008-06-17 : 16:38:35

Hi guys,

I have a table which consist of 6,185 rows and from this table I want to delete 427 rows. I have placed the 427 rows in a separate table. So how do I delete these 427 from the original table(6185 rows).

I should also mention that the table does not have a primary key so I was thinking about something like this but it didn't work


delete  o.CardNumber 
	,o.ref_no
	,o.tran_date 
	,o.tran_val
	
from  test6185  o

where     exists (select  h.CardNumber
			,h.ref_no
			,h.tran_date
			,h.tran_val
		from test427    h
		where(    h.ref_no=o.ref_no and
			h.CardNumber = o.CardNumber and
			h.tran_date =o.tran_date and 
			h.tran_val =o.tran_val 
			))

order by cardnumber

Thank much for any help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-17 : 16:48:05

Try



delete  o
from  test6185  o
where     exists (select  *
		from test427    h
		where(    h.ref_no=o.ref_no and
			h.CardNumber = o.CardNumber and
			h.tran_date =o.tran_date and 
			h.tran_val =o.tran_val 
			))

Madhivanan

Failing to plan is Planning to fail

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-17 : 16:50:03

It's always helpful to let us know what you mean by "it didn't work".

Try this:


DELETE o
FROM test6185 o
INNER JOIN test427 h
ON	h.ref_no = o.ref_no AND 
	h.CardNumber = o.CardNumber AND
	h.tran_date = o.tran_date AND 
	h.tran_val = o.tran_val

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-06-17 : 17:53:55

An ORDER BY clause is not valid in a delete statement.

http://msdn.microsoft.com/en-us/library/ms189835.aspx

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources