Deleting Records in a Table

Hi,
I have a table which has some columns which have repititive values. I want to keep the first value(record) of column(which has the repitive values) and then delete the other records which repeat. Please let me know.
Thanks.
Example:
Table:
column 1 Column2 Column3 Column4
1 10 23 15
2 12 26 14
3 13 25 14
4 100 250 14
I want to delete records number 3-4 but retain the 2nd record.Hello!

Is column1 an ID-column in this table? If yes, you can identify the record to delete with the following query

select * from table_b t1
where t1.column1 not in (select min(t2.column1)
from dbo.table_b t2
where t2.column4 = t1.column4)

If no, you should look up this post http://www.dbforums.com/t926686.html.

If there are more quetions, post again!

Greetings,
Carsten|||Originally posted by CarstenK
Hello!

Is column1 an ID-column in this table? If yes, you can identify the record to delete with the following query

select * from table_b t1
where t1.column1 not in (select min(t2.column1)
from dbo.table_b t2
where t2.column4 = t1.column4)

If no, you should look up this post http://www.dbforums.com/t926686.html.

If there are more quetions, post again!

Greetings,
Carsten
Hi Carsten.
Thanks for the SQL, but I am a little bit confused about the Query! I have just one table, but in your query you have stated Table_t1 and Table_t2...Please let me know.
Thanks.|||Hi,

the only table used in this query should be "table_b". But this one twice! If you have a query accessing the same table more than once (like here, in the query and sub query) you should use the synonyms to ensure which table you exactly mean. The use of synonyms is like this:

<synonym>.<column name>

So, just one table is used, but in two different forms.

Greetings,
Carsten|||Originally posted by CarstenK
Hi,

the only table used in this query should be "table_b". But this one twice! If you have a query accessing the same table more than once (like here, in the query and sub query) you should use the synonyms to ensure which table you exactly mean. The use of synonyms is like this:

<synonym>.<column name>

So, just one table is used, but in two different forms.

Greetings,
Carsten

Where and How do we Delete the repititive records from the Original Table?
Thanks Again!|||Now that you know (and see) which record to delete, you only need to exchange the "SELECT *"-statement for the "DELETE"-statement.

Carsten|||Originally posted by CarstenK
Now that you know (and see) which record to delete, you only need to exchange the "SELECT *"-statement for the "DELETE"-statement.

Carsten
Thank you, Mr. Carsten!!!!!|||Originally posted by CarstenK
Now that you know (and see) which record to delete, you only need to exchange the "SELECT *"-statement for the "DELETE"-statement.

Carsten
Hi Carsten,
I am using Sybase Central and as a result only the Query with Select statement in it is working but when I replace it with Delete...it is not working! Any suggestions for this??|||Hi there,

the normal syntax for delete looks like

delete from <table_name>
[where <where_condition>

Carsten|||You probably left the * after the delete statement, which is MS Access syntax but is not acceptable in SQL Server.

Here is my preferred method, using joins instead of where clause:

delete
from YourTable
inner join
(select column4, min(column1) column1
from YourTable
group by column4) FirstValues
on YourYable.column4 = FirstValues.column4
where YourTable.column1 > FirstValues.column1

WHERE clauses with subquerys and NOT IN statements are not as efficient as table joins, although in some cases the optimizer can convert the syntax to a JOIN prior to developing an execution plan.

blindman|||Hi, I used the owner moon:

create table moon.tempt
(tid integer primary key,
value integer)

insert into moon.tempt values( 1, 15)
insert into moon.tempt values( 2, 14)
insert into moon.tempt values( 3, 14)
insert into moon.tempt values( 4, 14)

delete t1
from moon.tempt t1
where
t1.tid <> (select min(t2.tid)
from moon.tempt t2
where t2.value = t1.value)

finally:

select * from moon.tempt

tid value
---- ----
1 15
2 14|||Again, while the optimizer might be able to convert this into standard JOIN syntax, if it cannot then you are essentially asking SQL Server to run

select min(t2.tid) from moon.tempt t2 where t2.value = t1.value

...once for every record in table tempt. Not as efficient as a JOIN clause which only executes the subquery once.

Also, the "<>" operator is particularly ineffecient, because it is generally non-sargable and cannot take advantage of indexes. As a matter of fact, it is the least efficient of all the comparison operators.

blindman

Deleting min value from grouped records

I have a table where no keys are currently defined, so we have dups...kind
of. In this table the account number with be the primary key, and we also
have a date field. There are records in there that have the same account
number, but a different date. I want to find the duplicates, which is the
easy part. Then from there I want to delete the record that has the oldest
date. Example
record 1
account 1
date 1-1-2004
record 2
account 1
date 5-1-2004
I want to delete record 1. I am having trouble coming up with the code.
Any help is appreciated.
ThanksTry,
delete t1
where exists(select * from t1 as a where a.account_id = t1.account_id and
a.col_date > t1.col_date)
This will not eliminate duplicated rows with same col_date.
AMB
"Andy" wrote:

> I have a table where no keys are currently defined, so we have dups...kin
d
> of. In this table the account number with be the primary key, and we also
> have a date field. There are records in there that have the same account
> number, but a different date. I want to find the duplicates, which is the
> easy part. Then from there I want to delete the record that has the oldes
t
> date. Example
> record 1
> account 1
> date 1-1-2004
> record 2
> account 1
> date 5-1-2004
> I want to delete record 1. I am having trouble coming up with the code.
> Any help is appreciated.
> Thanks|||Delete SomeTable
from SomeTable
INNER JOIN
(
Select MIN([Date]), account from SomeTable
Group by account
) Subquery
on
Subquery.record = SomeTable.Record AND
Subquery.account = SomeTable.account AND
Subquery.[date] = SomeTable.[date]
HTH, Jens SUessmeyer.
"Andy" <Andy@.discussions.microsoft.com> schrieb im Newsbeitrag
news:4F5EBDA6-6BAE-44D0-9413-869F150B3640@.microsoft.com...
>I have a table where no keys are currently defined, so we have dups...kind
> of. In this table the account number with be the primary key, and we also
> have a date field. There are records in there that have the same account
> number, but a different date. I want to find the duplicates, which is the
> easy part. Then from there I want to delete the record that has the
> oldest
> date. Example
> record 1
> account 1
> date 1-1-2004
> record 2
> account 1
> date 5-1-2004
> I want to delete record 1. I am having trouble coming up with the code.
> Any help is appreciated.
> Thanks|||Are you sure you "want to delete the record with the oldest Date" and that's
all? W
--What if there is only one record?
-- What if there are morethan 2 records?
Most of the time what is desired is t odelete ALL BUT The most recent
record... whichis actually easier..
But...
Delete T
From Table T
Where DateCol =
(Select Min(DateCol) From Table
Where AccountNo = T.AccountNo)
-- Add this if you only want to delete when there are dupes with same
accountNo
And Exists (Select * From Table
Where AcountNo = T.AccountNo
And DateCol > T.DateCol)
This will delete all reco
"Andy" wrote:

> I have a table where no keys are currently defined, so we have dups...kin
d
> of. In this table the account number with be the primary key, and we also
> have a date field. There are records in there that have the same account
> number, but a different date. I want to find the duplicates, which is the
> easy part. Then from there I want to delete the record that has the oldes
t
> date. Example
> record 1
> account 1
> date 1-1-2004
> record 2
> account 1
> date 5-1-2004
> I want to delete record 1. I am having trouble coming up with the code.
> Any help is appreciated.
> Thanks

Delete's Value

Has anyone ever had an issue when they try to commit changes back to the database based on a value they enter in a application...the value that u would use (i.e Select * From Customers Where CustomerID = 'value') to get the rest of the value's pertaining to that value you enter gets deleted from the database....its really wierd but i have that problemYou mean that rows have been deleted by selecting them ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||I mean the one value i enter in my form gets deleted all the information from that row stay's except for the search value that i use...|||

"My form" is not really descriptive enough. Is this a tool issue? Or a SQL issue with code you have written to support a form?

If it is SQL, can you post some of the table structures? If it is the former, I would probably suggest you use profiler to get a view of the queries being sent and try to match that to what you expect to happen.

|||How would i use the SQL profiler to see whats being sent...I have not used it before but ill check the net too...thanks

Delete Trigger

Dear All
I am trying to create a DELETE Trigger on a table which will delete a
previous record for a given value entered.
[1] I create a table with two columns called ItemName, TimeStamp (when the
record was last inserted/updated).
[2] I Insert a value of 'Table' into ItemName.
[3] I Insert a value of 'Chair' into ItemName
[4] If I add another entry of 'Table' into ItemName and the Trigger will
find the previous entry for 'Table' and delete it.
What I don't understand is how to get the value on the newly added column
and then serach for the 'duplicate' record to delete?
I apologise if I am not clear as I am a newbie at SQL Server.
Thanks for the continued support of the group and especially Louis Davidson
who helped me with a similar post.
Alastair MacFarlaneyou dont need a triger for delete, you need a trigger for insert that
deletes the record. In the trigger, you have a table 'inserted'. Table
'inserted' has new data, or data you're inserting. So you can write
something like:
CREATE TRIGGER trigerName
ON Table
FOR INSERT
AS
BEGIN
delete
from
table t
inner join inserted i on t.itemname = i.itemname
where
t.timestamp < i.timestamp
END
This will delete ALL records from table with the same itemName and lower
timestamp. You also might want to think about updates.
What should happen if someone updates the ItemName column?
MC
"Alastair MacFarlane" <AlastairMacFarlane@.discussions.microsoft.com> wrote
in message news:8306D794-88DB-4C39-942D-BF62E7822170@.microsoft.com...
> Dear All
> I am trying to create a DELETE Trigger on a table which will delete a
> previous record for a given value entered.
> [1] I create a table with two columns called ItemName, TimeStamp (when the
> record was last inserted/updated).
> [2] I Insert a value of 'Table' into ItemName.
> [3] I Insert a value of 'Chair' into ItemName
> [4] If I add another entry of 'Table' into ItemName and the Trigger will
> find the previous entry for 'Table' and delete it.
> What I don't understand is how to get the value on the newly added column
> and then serach for the 'duplicate' record to delete?
> I apologise if I am not clear as I am a newbie at SQL Server.
> Thanks for the continued support of the group and especially Louis
> Davidson
> who helped me with a similar post.
> Alastair MacFarlane|||Hi
I think you need a trigger for INSERT
Try this one (untested)
create table r (col1 int not null primary key,
col2 char(1) not null)
create trigger my_tr on r for insert
as
if exists (select count(*),col2 from
r group by col2 having count(*)>1)
delete r
from r join inserted i on r.col2=i.col2 and r.col1<i.col1
--test
insert into r values (1,'a')
insert into r values (2,'b')
insert into r values (3,'c')
insert into r values (4,'c')
select * from r
"Alastair MacFarlane" <AlastairMacFarlane@.discussions.microsoft.com> wrote
in message news:8306D794-88DB-4C39-942D-BF62E7822170@.microsoft.com...
> Dear All
> I am trying to create a DELETE Trigger on a table which will delete a
> previous record for a given value entered.
> [1] I create a table with two columns called ItemName, TimeStamp (when the
> record was last inserted/updated).
> [2] I Insert a value of 'Table' into ItemName.
> [3] I Insert a value of 'Chair' into ItemName
> [4] If I add another entry of 'Table' into ItemName and the Trigger will
> find the previous entry for 'Table' and delete it.
> What I don't understand is how to get the value on the newly added column
> and then serach for the 'duplicate' record to delete?
> I apologise if I am not clear as I am a newbie at SQL Server.
> Thanks for the continued support of the group and especially Louis
> Davidson
> who helped me with a similar post.
> Alastair MacFarlane|||An instead of trigger would do well for this
CREATE TABLE furniture (ItemName varchar(512), [TimeStamp] timestamp)
GO
INSERT INTO furniture (ItemName) VALUES ('TABLE')
INSERT INTO furniture (ItemName) VALUES ('CHAIR')
SELECT ItemName, [TimeStamp] FROM furniture
/*
ItemName TimeStamp
---
TABLE 0x0000000000002025
CHAIR 0x0000000000002026
*/
DROP TRIGGER trigger1
GO
CREATE TRIGGER trigger1 ON furniture
INSTEAD OF INSERT
AS
SET NOCOUNT ON
UPDATE furniture SET ItemName = i.ItemName
FROM inserted i INNER JOIN furniture f ON f.ItemName = i.ItemName
INSERT INTO furniture (ItemName) SELECT ItemName FROM inserted i
WHERE NOT EXISTS (SELECT 1 FROM furniture f WHERE i.ItemName = f.ItemName)
SET NOCOUNT OFF
INSERT INTO furniture (ItemName) VALUES ('Ottoman')
INSERT INTO furniture (ItemName) VALUES ('TABLE')
SELECT ItemName, [TimeStamp] FROM furniture
/*
ItemName TimeStamp
-- --
TABLE 0x0000000000002029
CHAIR 0x0000000000002026
Ottoman 0x0000000000002028
*/
--
"Alastair MacFarlane" wrote:

> Dear All
> I am trying to create a DELETE Trigger on a table which will delete a
> previous record for a given value entered.
> [1] I create a table with two columns called ItemName, TimeStamp (when the
> record was last inserted/updated).
> [2] I Insert a value of 'Table' into ItemName.
> [3] I Insert a value of 'Chair' into ItemName
> [4] If I add another entry of 'Table' into ItemName and the Trigger will
> find the previous entry for 'Table' and delete it.
> What I don't understand is how to get the value on the newly added column
> and then serach for the 'duplicate' record to delete?
> I apologise if I am not clear as I am a newbie at SQL Server.
> Thanks for the continued support of the group and especially Louis Davidso
n
> who helped me with a similar post.
> Alastair MacFarlane|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:583593
Alastair,
I'm not sure you really want to create a duplicate and delete it. Here
is another way (Uri, thanks for setting up the data):
create table r (col1 int not null primary key,
col2 char(1) not null)
go
create view r_v
as
select * from r
go
create trigger my_tr on r_v
instead of insert
as
begin
update r set r.col2 = inserted.col2
from r,inserted
where r.col1=inserted.col1
insert into r
select inserted.col1, inserted.col2
from inserted left outer join r
on r.col1=inserted.col1
where r.col1 is null
end
go
--test
insert into r values (1,'a')
insert into r values (2,'b')
insert into r values (3,'c')
-- this will update the row with col1=1
insert into r_v values (1,'c')
go
select * from r
col1 col2
-- --
1 c
2 b
3 c
go
insert into r_v
-- this row is inserted
select 4, 'D'
union all
-- this row is updated
select 2, 'e'
go
select * from r
col1 col2
-- --
1 c
2 e
3 c
4 D
(4 row(s) affected)
go
drop table r
drop view r_v|||Sorry, I thought from your DDL that you were using a column with a datatype
of timestamp. Here is the trigger re-written for a column of datatype
datetime.
DROP TABLE furniture
GO
CREATE TABLE furniture (ItemName char(20), [TimeStamp] datetime)
GO
INSERT INTO furniture VALUES ('TABLE',GETDATE())
INSERT INTO furniture VALUES ('CHAIR',GETDATE())
SELECT ItemName, [TimeStamp] FROM furniture
/*
ItemName TimeStamp
-- --
TABLE 2006-02-09 08:38:35.173
CHAIR 2006-02-09 08:38:35.190
*/
DROP TRIGGER trigger1
GO
CREATE TRIGGER trigger1 ON furniture
INSTEAD OF INSERT
AS
SET NOCOUNT ON
UPDATE furniture SET [TimeStamp] = GETDATE()
FROM inserted i INNER JOIN furniture f ON f.ItemName = i.ItemName
INSERT INTO furniture SELECT ItemName, GETDATE() FROM inserted i
WHERE NOT EXISTS (SELECT 1 FROM furniture f WHERE i.ItemName = f.ItemName)
SET NOCOUNT OFF
INSERT INTO furniture (ItemName) VALUES ('Ottoman')
INSERT INTO furniture (ItemName) VALUES ('TABLE')
SELECT ItemName, [TimeStamp] FROM furniture
/*
ItemName TimeStamp
-- --
TABLE 2006-02-09 08:39:45.410
CHAIR 2006-02-09 08:38:35.190
Ottoman 2006-02-09 08:39:43.440
*/
"Mark Williams" wrote:
> An instead of trigger would do well for this
> CREATE TABLE furniture (ItemName varchar(512), [TimeStamp] timestamp)
> GO
> INSERT INTO furniture (ItemName) VALUES ('TABLE')
> INSERT INTO furniture (ItemName) VALUES ('CHAIR')
> SELECT ItemName, [TimeStamp] FROM furniture
> /*
> ItemName TimeStamp
> ---
> TABLE 0x0000000000002025
> CHAIR 0x0000000000002026
> */
> DROP TRIGGER trigger1
> GO
> CREATE TRIGGER trigger1 ON furniture
> INSTEAD OF INSERT
> AS
> SET NOCOUNT ON
> UPDATE furniture SET ItemName = i.ItemName
> FROM inserted i INNER JOIN furniture f ON f.ItemName = i.ItemName
> INSERT INTO furniture (ItemName) SELECT ItemName FROM inserted i
> WHERE NOT EXISTS (SELECT 1 FROM furniture f WHERE i.ItemName = f.ItemNam
e)
> SET NOCOUNT OFF
> INSERT INTO furniture (ItemName) VALUES ('Ottoman')
> INSERT INTO furniture (ItemName) VALUES ('TABLE')
> SELECT ItemName, [TimeStamp] FROM furniture
> /*
> ItemName TimeStamp
> -- --
> TABLE 0x0000000000002029
> CHAIR 0x0000000000002026
> Ottoman 0x0000000000002028
> */
> --
> "Alastair MacFarlane" wrote:
>|||Mark and others,
Sorry for the delay and thanks for the feedback. I have tested out the
examples provided and they work the way I would expect.
Your help is appreciated.
Alastair MacFarlane
"Mark Williams" wrote:
> Sorry, I thought from your DDL that you were using a column with a datatyp
e
> of timestamp. Here is the trigger re-written for a column of datatype
> datetime.
> DROP TABLE furniture
> GO
> CREATE TABLE furniture (ItemName char(20), [TimeStamp] datetime)
> GO
> INSERT INTO furniture VALUES ('TABLE',GETDATE())
> INSERT INTO furniture VALUES ('CHAIR',GETDATE())
> SELECT ItemName, [TimeStamp] FROM furniture
> /*
> ItemName TimeStamp
> -- --
> TABLE 2006-02-09 08:38:35.173
> CHAIR 2006-02-09 08:38:35.190
> */
> DROP TRIGGER trigger1
> GO
> CREATE TRIGGER trigger1 ON furniture
> INSTEAD OF INSERT
> AS
> SET NOCOUNT ON
> UPDATE furniture SET [TimeStamp] = GETDATE()
> FROM inserted i INNER JOIN furniture f ON f.ItemName = i.ItemName
> INSERT INTO furniture SELECT ItemName, GETDATE() FROM inserted i
> WHERE NOT EXISTS (SELECT 1 FROM furniture f WHERE i.ItemName = f.ItemNam
e)
> SET NOCOUNT OFF
> INSERT INTO furniture (ItemName) VALUES ('Ottoman')
> INSERT INTO furniture (ItemName) VALUES ('TABLE')
> SELECT ItemName, [TimeStamp] FROM furniture
> /*
> ItemName TimeStamp
> -- --
> TABLE 2006-02-09 08:39:45.410
> CHAIR 2006-02-09 08:38:35.190
> Ottoman 2006-02-09 08:39:43.440
> */
> --
> "Mark Williams" wrote:
>

DELETE Trigger

Hi,
I am pretty sure this is fairly straightforward, I would like to use a
trigger to delete an existing record from a table where the value of certain
fields match those of the record to be inserted.
Thanks.Hi Redowl,
CREATE TRIGGER SomeDelTrigger ON SomeTable
FOR INSERT
AS
BEGIN
DELETE
FROM SomeOtherTable
INNER JOIN INSERTED
ON SomeOtherTable.COl01 = INSERTED.Col01
-- (...) other columns to join on
END
HTH, Jens Suessmeyer|||Without knowing the details of your underlying tables
CREATE TRIGGER deleteRows ON [YourTable]
AFTER INSERT
AS
DELETE FROM [YourTable] WHERE [YourTable].col1=inserted.col1
AND [YourTable].col2=inserted.col2 --check to match more if necessary
Keep in mind that an AFTER trigger will only execute if the statement that
caused the trigger to fire would have normally succeeded. If you are trying
to delete a row that would have caused a violation of a constraint, use a
INSTEAD OF trigger.
--
"Redowl" wrote:

> Hi,
> I am pretty sure this is fairly straightforward, I would like to use a
> trigger to delete an existing record from a table where the value of certa
in
> fields match those of the record to be inserted.
> Thanks.
>
>|||CREATE TRIGGER SomeDelTrigger ON SomeTable
FOR INSERT
AS
BEGIN
DELETE
FROM SomeotherTable
INNER JOIN INSERTED
ON SomeotherTable.Col1 = INSERTED.Col1
--(...) and so on, name the joined columns
END
HTH, jens Suessmeyer.|||Thanks everybody for the responses
Mark, will your suggestion not delete the newly added row as well ?
"Mark Williams" wrote:
> Without knowing the details of your underlying tables
> CREATE TRIGGER deleteRows ON [YourTable]
> AFTER INSERT
> AS
> DELETE FROM [YourTable] WHERE [YourTable].col1=inserted.col1
> AND [YourTable].col2=inserted.col2 --check to match more if necessary
>
> Keep in mind that an AFTER trigger will only execute if the statement that
> caused the trigger to fire would have normally succeeded. If you are tryin
g
> to delete a row that would have caused a violation of a constraint, use a
> INSTEAD OF trigger.
> --
>
> "Redowl" wrote:
>|||Jens,
Thanks for the response. I want to delete an existing row from the same
table I am inserting into.
I want to delete any existing rows that match a certain criteria. e.g A new
row could have surname = 'Jones' and firstname = 'Bob' and age='30'. I want
to check if there is already a row with surname = 'Jones' and firstname =
'Bob' and delete this?
Grateful for any help.
"Jens" wrote:

> CREATE TRIGGER SomeDelTrigger ON SomeTable
> FOR INSERT
> AS
> BEGIN
> DELETE
> FROM SomeotherTable
> INNER JOIN INSERTED
> ON SomeotherTable.Col1 = INSERTED.Col1
> --(...) and so on, name the joined columns
> END
>
> HTH, jens Suessmeyer.
>

Delete syntax to delete a record from one table if a matching value isn't found in another

I'm trying to clean up a database design and I'm in a situation to where two tables need a FK but since it didn't exist before there are orphaned records.

Tables are:

Brokers and it's PK is BID

The 2nd table is Broker_Rates which also has a BID table.

I'm trying to figure out a t-sql statement that will parse through all the recrods in the Broker_Rates table and delete the record if there isn't a match for the BID record in the brokers table.

I know this isn't correct syntax but should hopefully clear up what I'm asking

DELETE FROM Broker_Rates

WHERE (Broker_Rates.BID <> Broker.BID)

Thanks

kfrost:

Maybe something like:

DELETE FROM Broker_Rates
from Broker_Rates a
WHERE not exists
( select 0 from Broker b
where a.BID = B.id
)

Dave

|||

That appeared to do the trick. Curious, what does using the 0 in Select 0 person of your string above accomplish. I was using Select *.

Works, just curious for future reference.

Thanks.

|||

I use the zero because the select list is not relevant. Your syntax will work fine. I need to do something like "0 as dummy" so that it is more obvious that the item is a dummy item.

Dave

|||Cool. Thanks!!

Delete row where a particular field value is not in a list

I have a table which has a field called filename, thus the data in the field would look something like this:

FileName.doc
FileName.txt
FileName.foo

I need to delete several rows from this table where the filename field DOES NOT contain the following extensions (e.g. row with FileName.foo would be deleted):

.txt
.pdf
.rtf
.doc
.htm
.html
.mht
.mhtml
.dot
.wk1
.wk3
.wk4
.xls
.xlw
.asc
.olk
.pab
.scd
.ans
.wri
.mcw
.wpd
.wps
.ppt
.pps
.xls
.xlt
.xlw

Crazy method for identifing file extension:

declare @.filename varchar(100)

set @.filename= 'filename.txt'

select charindex('.',@.filename,-1) --Position of last dot in @.filename

select substring(@.filename,charindex('.',@.filename,-1),len(@.filename)-charindex('.',@.filename,-1)+1)

--result '.txt'

Than you could use "IN keyword"

PS. With query works very slow

|||

create table mytable(filename varchar(20))
insert into mytable(filename) values('FileName.doc')
insert into mytable(filename) values('FileName.txt')
insert into mytable(filename) values('FileName.foo')

create table allowed(ext varchar(8))
insert into allowed(ext)
select '.txt' union all
select '.pdf' union all
select '.rtf' union all
select '.doc' union all
select '.htm' union all
select '.html' union all
select '.mht' union all
select '.mhtml' -- etc

delete from mytable
where not exists(select * from allowed where filename like '%'+ext)

|||Marks answer seems most appropriate because it allows me to keep a table of "allowable" extensions. But I am open to other ideas if anyone else wants to contribute.

DELETE Procedure. How to return a value?

Hello,

I created a DELETE Stored Procedure in SQL 2005.
When calling this procedure from my server code (VB.NET in my case) I
need to know if the record was deleted or not.

How should I do this?

Should I make the procedure to return True or False? If yes, how can I
do this?

My Stored Procedure is as follows (I think it is ok):

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Content_DeleteContent]

-- Define the procedure parameters
@.ContentName NVARCHAR(100),
@.ContentCulture NVARCHAR(5)

AS

-- Prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;

-- Declare and define ContentId
DECLARE @.ContentId UNIQUEIDENTIFIER;
SELECT @.ContentId = ContentId FROM dbo.Content WHERE ContentName =
@.ContentName

-- Check if ContentId is Not Null
IF @.ContentId IS NOT NULL
BEGIN

-- Check if ContentId is Null
IF @.ContentCulture IS NULL
BEGIN

-- Delete all localized contents from dbo.ContentLocalized
DELETE
FROM dbo.ContentLocalized
WHERE ContentId = @.ContentId

-- Delete content from dbo.Content
DELETE
FROM dbo.Content
WHERE ContentName = @.ContentName;

END
ELSE

-- Delete localized content from dbo.ContentLocalized
DELETE
FROM dbo.ContentLocalized
WHERE (ContentID = @.ContentID AND ContentCulture = @.ContentCulture)

END

GO

Thanks,
Miguel

hi miguel.

below is the sp i modified. i've added an output parameter, which will be null if record(s) are deleted properly. so you can check in your code using output parameter.

SET ANSI_NULLSON
GO
SET QUOTED_IDENTIFIERON
GO

ALTER PROCEDURE [dbo].[Content_DeleteContent]

-- Define the procedure parameters
@.ContentNameNVARCHAR(100),
@.ContentCultureNVARCHAR(5) ,
@.nFlag tinyint output

AS

-- Prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;

-- Declare and define ContentId
DECLARE @.ContentIdUNIQUEIDENTIFIER;
SELECT @.ContentId = ContentIdFROM dbo.ContentWHERE ContentName =
@.ContentName

-- Check if ContentId is Not Null
IF @.ContentIdISNOT NULL
BEGIN

-- Check if ContentId is Null
IF @.ContentCultureISNULL
BEGIN

-- Delete all localized contents from dbo.ContentLocalized
DELETE
FROM dbo.ContentLocalized
WHERE ContentId = @.ContentId

-- Delete content from dbo.Content
DELETE
FROM dbo.Content
WHERE ContentName = @.ContentName;

Select @.nFlag = ContentIDfrom dbo.ContentLocalized
WHERE ContentId = @.ContentId
if @.nFlagisnull
Begin
Select @.nFlag = 1FROM dbo.Content
WHERE ContentName = @.ContentName;
End

END
ELSE

BEGIN
-- Delete localized content from dbo.ContentLocalized
DELETE
FROM dbo.ContentLocalized
WHERE (ContentID = @.ContentID AND ContentCulture = @.ContentCulture)

Select @.nFlage = ContentIDFROM dbo.ContentLocalized
WHERE (ContentID = @.ContentID AND ContentCulture = @.ContentCulture)

END

END

hope it helps.

regards,

satish.|||Rather than do SELECT's after the DELETE's checking for @.@.ROWCOUNT works faster since the number of records deleted is already stored in the function. You could just check if @.@.ROWCOUNT >0 and set your flag to 1 or 0 appropriately.|||

Hi Dinakar

miguel has usedSET NOCOUNT ON.

thanks,

satish.

|||

Hello,

I defined "Set NoCount On" because this is done by default in SQL Server 2005 Management Studio and because this is how I see in most code examples.

I am just starting with SQL. Should I change it?

Could somebody give me some info on this?

Thanks,

Miguel

|||

hi miguel,

sorry i m not aware of default in sql 2k5

set nocount on --in case you update/insert/delete record on a table it wont return number of affected records

set nocount off --in case you update/insert/delete record on a table it will return number of affected records

hope it clarifies

now if you set it to off in your sp then after delete you can check

if @.@.ROWCOUNT > 0

begin

end

@.@.ROWCOUNT contains no.of records affected due to preceeding operation.

regards,

satish.

|||

SET NOCOUNT will only suppress messages from being printed in the Messages tab which are returned to the calling application. It does not have any effect on @.@.ROWCOUNT. You can still do your T-SQL Queries and have the results of rows affected in @.@.ROWCOUNT, get the value into variable and return it via OUTPUT parameters. Having the SET NOCOUNT ON is a good thing, generally. It prevents unnecessary messages from being passed across to the application.

Satish,

You are probably checking the Messages tab by running a T-SQL statement between SET NOCOUNT commands.

Try this:

SET NOCOUNT ON

SELECT TOP 10 * FROM anyTable

SELECT @.@.ROWCOUNT as RowsAffected

SET NOCOUNT OFF

|||

errrrr silly of me.

thanks dinakar you removed my misunderstanding about @.@.ROWCOUNT and SET NOCOUNT

cheers,

satish.