Deleting semi duplicates

Suppose that I have a table that contains a lot of records that are
identical except for an id field and a date-time-stamp field. For
example

Id Unit Price DTS
1 A 1.00 Date 1
2 A 1.00 Date 2
3 A 1.00 Date 3
4 B 1.25 Date 4
5 B 1.50 Date 5
6 B 1.50 Date 6
7 C 2.75 Date 7
8 C 2.75 Date 8
9 C 2.75 Date 9
10 C 3.00 Date 10

I want to cull out records that are duplicates in the units and price
fields. I want to use the max DTS as the criteria for which record in
a set of "duplicates" will remain. So, If I get the right query, I
should return with

Id Unit Price DTS
1 A 1.00 Date 1
4 B 1.25 Date 4
5 B 1.50 Date 5
7 C 2.75 Date 7
10 C 3.00 Date 10

Is this possible using a single query? If so, how? I am sure that I
can do this using code, but it will involve a bunch of loops and
process time. I would prefer a cleaner, more elegant way. Thanks for
any help.

JerryAssuming the combination of (unit,price,dts) is unique and non-NULL:

DELETE FROM Sometable
WHERE EXISTS
(SELECT *
FROM Sometable AS S
WHERE unit = Sometable.unit
AND price = Sometable.price
AND dts > Sometable.dts)

--
David Portas
----
Please reply only to the newsgroup
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<zemdnRnYsNSQopHdRVn-vw@.giganews.com>...
> Assuming the combination of (unit,price,dts) is unique and non-NULL:
> DELETE FROM Sometable
> WHERE EXISTS
> (SELECT *
> FROM Sometable AS S
> WHERE unit = Sometable.unit
> AND price = Sometable.price
> AND dts > Sometable.dts)

Thanks. I'll give this a try.

J|||Remember that names are resoved to the nearer containing table
reference! You meant:

DELETE FROM Sometable
WHERE EXISTS
(SELECT *
FROM Sometable AS S
WHERE S.unit = Sometable.unit
AND S.price = Sometable.price
AND S.dts > Sometable.dts);|||> Remember that names are resoved to the nearer containing table
> reference!

Precisely. That's why the S isn't needed here - the alias ensures that
"Sometable" refers to the outer reference and the other columns to the inner
reference. Your statement is equivalent to mine.

--
David Portas
----
Please reply only to the newsgroup
--sql

deleting rows based on nvarchar data

I have a table that contains rows that I would like to delete based on a field and it's contents.
What is the correct syntax to script the removal of these rows based field parameter?delete tableA where fieldB = ?

Is that what you mean?|||Kinda. I only have one table and want to delete specific rows from that table that have a specific data within a certain field.

Let be more specific. I have a table (tableA) with 10 fields. Field 3 has data that does not conform to a datetime format and I would like to remove it. The field is currently a nvarchar(50) type (2003-10-10).

I want to remove rows that contain data that is looks like this
(0020-10-10). Make sense?|||delete from table where field3 ='0020-10-10'|||Perhaps you can use the ISDATE() function, which returns 1 if a string can be converted to a valid date, and 0 if it cannot.

Try this query:

select *
from YourTable
where ISDATE([Column3]) = 0

If this returns the rows you want deleted, then change the query to a delete query:

delete
from YourTable
where ISDATE([Column3]) = 0|||right but I forgot to mention, there are all kinds of variation of that date.

I ran a script that reads as follows to help identify data within a field that does not fit a date format->

SELECT * FROM findet WHERE ISDATE(servfrom) = 0

This gave me a list of records that are not in proper date format. Now, I would like to remove them from my table. Can I use the same,

delete findet where ISDATE(servfrom)=0|||See previous post.|||That worked like a charm, thank you!!

Deleting repeated data

The table contains data as follows,

SAP_CUS TOMER_NBRSAP_CUSTOMER_NBR1
A B
B A
C D
D C
C E
E C

A=B is same as B=A

The final table should be as follows,

SAP_CUSTOMER_NBRSAP_CUSTOMER_NBR1
A B
C D
C E

please help me in doing this

Quote:

Originally Posted by venkat81

The table contains data as follows,

SAP_CUS TOMER_NBRSAP_CUSTOMER_NBR1
A B
B A
C D
D C
C E
E C

A=B is same as B=A

The final table should be as follows,

SAP_CUSTOMER_NBRSAP_CUSTOMER_NBR1
A B
C D
C E

please help me in doing this

Provided the duplication set is as you say a single character swapped round like you indicate, the principle is to extract the SAME AS records as you point out and remove them. The logic being to identify the row to extract and leave you with the result set you want. So.. how can this be done?.

Each character will have an ASCII representation, a numeric value. 'A'=65 and B='66' Added together they make 131 so over two rows we are going to see two values of 131.

If we created a third column to store the ASCII value and worked on that we could extract out the MAXIMUM record ID identifying the row, extract those rows into a temporary table compare the tables against each other and remove the rows by the relevant SQL comparison on a LEFT INNER JOIN between the relevant tables.

One word of CAUTION here, The ascii values when summed will give a value remember that 8+5=12 so does 5+7 so the actual theory has limited scope
and is absolutely based on your data provided above, You do NOT want a summation giving you an erronous result value on which you base your delete.

Anyway below is a script I knocked up to assist you in demonstrating the HOWS!! not intended as the SOLUTION obviously as that is for you to deal with If you create a physical table ..like so

CREATE TABLE mytable (
[recid] [int] null ,
[sap_customer_nbr] [char] (1)null ,
[sap_customer_nbr1] [char] (1) null ,
[asciivalue] [int] null )

and populate it with data of the type you provided above and then run the script below in query analyser you will see that it works only on temporary tables, which you can adjust to suit your production environment when you are ready and happy it works for you.

--USE whateverdatabasenamehere --<<<replace database name with yours
SET NOCOUNT ON
--create a temporary table to store the values
CREATE TABLE #mytable (
[recid] [int] null ,
[sap_customer_nbr] [char] (1)null ,
[sap_customer_nbr1] [char] (1) null ,
[asciivalue] [int] null )
--and the insert into this table
INSERT #mytable
--values from the main production table
SELECT * from mytable --<<substitute your table name here the column orders must match
--then update the temporary table with --the ACSII numeric values representing the characters ie A and B summed together=131
UPDATE #mytable
SET asciivalue=ASCII(sap_customer_nbr) + ASCII(sap_customer_nbr1)
--create another temporary table
CREATE TABLE #mylink (
[asciivalue] [int] null ,
[recid] [int] null )
--into which we will insert
INSERT #mylink
-- the MAXIMUM RecordID (RecID) for each Asciivalue which essential only gives
-- ACTUAL recordID to identify the row that we wish ultimately to Delete from the table
SELECT #mytable.asciivalue, MAX(#mytable.recid) AS linkkey
FROM #mytable
GROUP BY #mytable.asciivalue

--the next two lines are left in to merely show you in query analyser the resultsets
SELECT * FROM #mytable
SELECT * FROM #mylink

--we now delete from the first temporary table the rows we wish to remove
DELETE #mytable
FROM #mylink INNER JOIN #mytable ON #mylink.recid = #mytable.recid
--and to finalise we show the results again in query analyser
SELECT * FROM #mytable
--and finally drop the temporary explicitly
DROP TABLE #mylink
DROP TABLE #mytable|||

Quote:

Originally Posted by Jim Doherty

Provided the duplication set is as you say a single character swapped round like you indicate, the principle is to extract the SAME AS records as you point out and remove them. The logic being to identify the row to extract and leave you with the result set you want. So.. how can this be done?.

Each character will have an ASCII representation, a numeric value. 'A'=65 and B='66' Added together they make 131 so over two rows we are going to see two values of 131.

If we created a third column to store the ASCII value and worked on that we could extract out the MAXIMUM record ID identifying the row, extract those rows into a temporary table compare the tables against each other and remove the rows by the relevant SQL comparison on a LEFT INNER JOIN between the relevant tables.

One word of CAUTION here, The ascii values when summed will give a value remember that 8+5=12 so does 5+7 so the actual theory has limited scope
and is absolutely based on your data provided above, You do NOT want a summation giving you an erronous result value on which you base your delete.

Anyway below is a script I knocked up to assist you in demonstrating the HOWS!! not intended as the SOLUTION obviously as that is for you to deal with If you create a physical table ..like so

CREATE TABLE mytable (
[recid] [int] null ,
[sap_customer_nbr] [char] (1)null ,
[sap_customer_nbr1] [char] (1) null ,
[asciivalue] [int] null )

and populate it with data of the type you provided above and then run the script below in query analyser you will see that it works only on temporary tables, which you can adjust to suit your production environment when you are ready and happy it works for you.

--USE whateverdatabasenamehere --<<<replace database name with yours
SET NOCOUNT ON
--create a temporary table to store the values
CREATE TABLE #mytable (
[recid] [int] null ,
[sap_customer_nbr] [char] (1)null ,
[sap_customer_nbr1] [char] (1) null ,
[asciivalue] [int] null )
--and the insert into this table
INSERT #mytable
--values from the main production table
SELECT * from mytable --<<substitute your table name here the column orders must match
--then update the temporary table with --the ACSII numeric values representing the characters ie A and B summed together=131
UPDATE #mytable
SET asciivalue=ASCII(sap_customer_nbr) + ASCII(sap_customer_nbr1)
--create another temporary table
CREATE TABLE #mylink (
[asciivalue] [int] null ,
[recid] [int] null )
--into which we will insert
INSERT #mylink
-- the MAXIMUM RecordID (RecID) for each Asciivalue which essential only gives
-- ACTUAL recordID to identify the row that we wish ultimately to Delete from the table
SELECT #mytable.asciivalue, MAX(#mytable.recid) AS linkkey
FROM #mytable
GROUP BY #mytable.asciivalue

--the next two lines are left in to merely show you in query analyser the resultsets
SELECT * FROM #mytable
SELECT * FROM #mylink

--we now delete from the first temporary table the rows we wish to remove
DELETE #mytable
FROM #mylink INNER JOIN #mytable ON #mylink.recid = #mytable.recid
--and to finalise we show the results again in query analyser
SELECT * FROM #mytable
--and finally drop the temporary explicitly
DROP TABLE #mylink
DROP TABLE #mytable

i have not read the entire solution, but i have one comment, 8 + 5 <> 12...|||

Quote:

Originally Posted by ck9663

i have not read the entire solution, but i have one comment, 8 + 5 <> 12...

It is where I come from hahahaha

Deleting records from two tables

im having a very blond day .. carnt get my head round this today
Im rining SQl2005
i have two tables ( A & B ) A contains the 'master' record abd B contains
the 'detail' For every record in A there will be a minimum of 1 record in B
to a max of 1000000
Table A is linked with table B by means of a.LedgerRef = b.LedgerRef
Table A also has a field 'Status'
What i want to do is create a stored procedure that deletes all records in
the Master (A) and Detail(B) tables when A.Status = 'T'
like i said this morning my minds a blanksomething like this?
begin tran
delete from B
from details B
where exists (select 1 from master_tbl A
where a.LedgerRef = b.LedgerRef
and a.status = 't')
delete from master_tbl
where status = 't'
commit tran
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Try this:
USE tempdb
GO
CREATE TABLE A
(
id int,
status char,
LedgerRef int
)
GO
CREATE TABLE B
(
LedgerRef int,
somedata varchar(100)
)
GO
INSERT A VALUES(1, 'T', 10)
INSERT A VALUES(2, 'F', 20)
INSERT B VALUES(10, 'delete it')
INSERT B VALUES(10, 'delete it')
INSERT B VALUES(20, 'don''t delete it')
DELETE B
FROM B JOIN A ON B.LedgerRef = A.LedgerRef
WHERE A.Status = 'T'
DELETE A
WHERE Status = 'T'
Greetings,
Urs
"Peter Newman" wrote:

> im having a very blond day .. carnt get my head round this today
> Im rining SQl2005
> i have two tables ( A & B ) A contains the 'master' record abd B contain
s
> the 'detail' For every record in A there will be a minimum of 1 record in
B
> to a max of 1000000
> Table A is linked with table B by means of a.LedgerRef = b.LedgerRef
> Table A also has a field 'Status'
> What i want to do is create a stored procedure that deletes all records in
> the Master (A) and Detail(B) tables when A.Status = 'T'
> like i said this morning my minds a blank
>|||Use CASCADE DELETE on TableB and you will only have to manage TableA.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:08FA6CBE-88EC-4775-80A1-6413FE99A16A@.microsoft.com...
> im having a very blond day .. carnt get my head round this today
> Im rining SQl2005
> i have two tables ( A & B ) A contains the 'master' record abd B
> contains
> the 'detail' For every record in A there will be a minimum of 1 record in
> B
> to a max of 1000000
> Table A is linked with table B by means of a.LedgerRef = b.LedgerRef
> Table A also has a field 'Status'
> What i want to do is create a stored procedure that deletes all records in
> the Master (A) and Detail(B) tables when A.Status = 'T'
> like i said this morning my minds a blank
>

Deleting million rows after checking duplication

I have a table(9 fields) which contains around 10 million records. Within these 10 million records, don't know how many are duplicated rows. I wrote a cursor which checks duplication on all of the 9 fields within each row, and then returns back with the count of rows that are duplicated. I subtact 1 row and delete all the other rows. Problem is that this thing takes a lot of time to execute. At the current rate(approx. 90 records/hour) this cursor is going to take months to clean the table.

There is no PK or no indexing what so ever on the table, and I HAVE to check each and every field for duplication(all except 1 fields are nvarchar).

Please help me. I need to sort this out

What about doing a grouping on the table and pulling hte data out to another table.

Something like:

Select cola,ColB,Colc (all columns here)
INTO SomeNewTable
FROM SomeTable
Group by cola,ColB,Colc (all columns here)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Deleting duplicate records

Hi All,
I am having one table named MyTable and this table contains only one column MyCol. Now i m having 10 records in it and all the records are duplicate ie value is 7 for all 10 records.

It is something like this,

MyCol
7
7
7
7
7
7
7
7
7
7

Now i m trying to delete 10th record or any record then it gives me error
"Key column information is insufficient or incorrect. Too many rows were affected by update."

What should i do if i want only 4 records insted 10 records in my table?
How do i delete the 6 records from table?

Plz help me.

Regards,
ShaileshSince there seems to be no (primary) key to identity the row there's nothing left than a workaround, which basically will delete all rows with, in this case, mycol on 7, and inserts four (your case) new records with the value 7.|||this could work for you:

set rowcount 4

delete from table_name

set rowcount 0 --set back to affect all rows

mojza|||Hi,
you can also temporary add one identity column and delete all the record that you dont need. After finish drop the new added identity column.

Deleting Duplicate Records

I have a table tmPunchtimeSummary which contains a sum of employee's hours
per day. The table contains some duplicates.

code:

CREATE TABLE [tmPunchtimeSummary]
(
[iTmPunchTimeSummaryId] [int] IDENTITY (1, 1) NOT NULL ,
[sCalldate] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sEmployeeId] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dTotalHrs] [decimal](18, 4) NULL
) ON [PRIMARY]
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '1234', 4.5)
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '1234', 4.5)
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '2468', 8.0)
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '1357', 9.0)
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '2345', 8.5)
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '2345', 8.5

How can I write a delete statement to only delete the duplicates which in
this case would be the 1st and 5th records?
Thanks,
Ninel
Message posted via http://www.webservertalk.comDELETE FROM tmPunchtimeSummary
WHERE EXISTS
(select * from tmPunchtimeSummary as X
where tmPunchtimeSummary.sCalldate = X.sCalldate
and tmPunchtimeSummary.sEmployeeId = X.sEmployeeId
and tmPunchtimeSummary.dTotalHrs = X.dTotalHrs
and tmPunchtimeSummary.iTmPunchTimeSummaryId <
X.iTmPunchTimeSummaryId)
Roy Harvey
Beacon Falls, CT
On Wed, 14 Jun 2006 16:41:01 GMT, "ngorbunov via webservertalk.com"
<
u9125@.uwe>
wrote:

>
I have a table tmPunchtimeSummary which contains a sum of employee's hours
>
per day. The table contains some duplicates.
>
>

code:

>
CREATE TABLE [tmPunchtimeSummary]
>
(
>
 [iTmPunchTimeSummaryId] [int] IDENTITY (1, 1) NOT NULL ,
>
 [sCalldate] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>
 [sEmployeeId] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>
 [dTotalHrs] [decimal](18, 4) NULL
>
 ) ON [PRIMARY]
>
>
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
>
VALUES('20060610', '1234', 4.5)
>
>
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
>
VALUES('20060610', '1234', 4.5)
>
>
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
>
VALUES('20060610', '2468', 8.0)
>
>
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
>
VALUES('20060610', '1357', 9.0)
>
>
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
>
VALUES('20060610', '2345', 8.5)
>
>
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
>
VALUES('20060610', '2345', 8.5
>

>
>
How can I write a delete statement to only delete the duplicates which in
>
this case would be the 1st and 5th records?
>
>
Thanks,
>
Ninel|||Thank you very much.
Roy Harvey wrote:
>DELETE FROM tmPunchtimeSummary
> WHERE EXISTS
> (select * from tmPunchtimeSummary as X
> where tmPunchtimeSummary.sCalldate = X.sCalldate
> and tmPunchtimeSummary.sEmployeeId = X.sEmployeeId
> and tmPunchtimeSummary.dTotalHrs = X.dTotalHrs
> and tmPunchtimeSummary.iTmPunchTimeSummaryId <
> X.iTmPunchTimeSummaryId)
>Roy Harvey
>Beacon Falls, CT
>
>[quoted text clipped - 32 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||> DELETE FROM tmPunchtimeSummary
> WHERE EXISTS
In my figuring on paper, Roy's solution works.
I also offer another suggetion: Microsoft Access has lots and lots of
wizards to do hard stuff like that. So if you have a copy of Access, let it
be your quick-and-dirty friend. You can use the Access wizard to buzz up
some stuff really fast, and then just paste the SQL code into QA, clean it
up a bit, and then run it.
But I will warn you: Access is not up to the task of production code.
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei

Deleting certain text patterns from a column

Hi,
I have a column in SQL DB and the column contains the information like:
<ProductDescription>This TV is good. </ProductDescription> This TV is sold
out.
<ProductDescription>This TV is bad. </ProductDescription> This TV is not
selling well.
(By the way, I am NOT talking about the XML-formatted SQL DB, which was
introduced in SQL 2000. The tag is just text mainly used for human
consumption.)
I want to delete all the text between <ProductDescription> and
</ProductDescription>, including the tags from the column. Is it possible?
It looks like the Replace function cannot take wildcard character and I am
thinking doing it programmatically, like with C#, is the only way. I
appreciate your help!Try something like this:
declare @.tag varchar(30)
declare @.test varchar(8000)
set @.test = 'Don''t get <tag> get rid of this </tag>rid of outside stuff'
set @.tag = 'tag'
select
stuff(@.test,charindex('<'+@.tag+'>',@.test),charindex('</'+@.tag+'>',@.test) +
len(@.tag) + 2,'')
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Kevin" <no_spam@.nospamfordiscussion.com> wrote in message
news:OfqSCkMYFHA.1736@.tk2msftngp13.phx.gbl...
> Hi,
> I have a column in SQL DB and the column contains the information like:
> <ProductDescription>This TV is good. </ProductDescription> This TV is sold
> out.
> <ProductDescription>This TV is bad. </ProductDescription> This TV is not
> selling well.
> (By the way, I am NOT talking about the XML-formatted SQL DB, which was
> introduced in SQL 2000. The tag is just text mainly used for human
> consumption.)
> I want to delete all the text between <ProductDescription> and
> </ProductDescription>, including the tags from the column. Is it possible?
> It looks like the Replace function cannot take wildcard character and I am
> thinking doing it programmatically, like with C#, is the only way. I
> appreciate your help!
>
>|||Thanks! Didn't think of using that function.
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:%23o1nh9MYFHA.2588@.TK2MSFTNGP14.phx.gbl...
> Try something like this:
> declare @.tag varchar(30)
> declare @.test varchar(8000)
> set @.test = 'Don''t get <tag> get rid of this </tag>rid of outside stuff'
> set @.tag = 'tag'
> select
> stuff(@.test,charindex('<'+@.tag+'>',@.test),charindex('</'+@.tag+'>',@.test) +
> len(@.tag) + 2,'')
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "Kevin" <no_spam@.nospamfordiscussion.com> wrote in message
> news:OfqSCkMYFHA.1736@.tk2msftngp13.phx.gbl...
>

deleting 11 000 000 rows...

Hi!
I just dicovered that one of our tables contains over 11 000 000 rows.
My problem right now is to delete a major part of these rows.
What I've done so far is to create a sp that handles this, but no matter how I do, the batch gets sent with all delete:s at the same time.

What I understand, the batch gets sent after the GO-statement, and at the same time, all my variables looses scope...

The data is organized on dates, one date may comprise several rows, and I know that one batch can handle all rows in one date...

The table is indexed but has no PK

The main SP:

CREATE PROCEDURE clear_lagertransaktioner
@.maxDate SMALLDATETIME
AS
DECLARE @.date SMALLDATETIME
DECLARE dateCur CURSOR FOR select distinct date from lagertransaktioner
OPEN dateCur

FETCH NEXT FROM dateCur INTO @.date
WHILE @.@.FETCH_STATUS = 0 AND @.date<@.maxDate
BEGIN

--Call another SP that I hoped would send the batch
exec del_post_lagertransaktioner @.date
FETCH NEXT FROM dateCur INTO @.date

END

CLOSE dateCur
DEALLOCATE dateCur
GO


The nested SP:
CREATE PROCEDURE del_post_lagertransaktioner
@.d SMALLDATETIME
AS

DELETE FROM lagertransaktioner WHERE date=@.d
GO

Does any of you have a better idea, cause this doesn't work.Mabye it is better for me to just insert needed data into a new table and drop the original table, since this is a once-in-a-lifetime situation (hopefully)

What do you say?

/Bix|||Two options.

Run SELECT INTO (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_9sfo.asp) and grab your small amount of rows and insert them into a new table. Then DROP the old table and re-name the new one. Provided you have an index to select from that should provide the lowest I/O.

If you want to batch it, do something like this:

SET ROWCOUNT 10000
WHILE @.@.ROWCOUNT > 0
DELETE FROM table_name WHERE etc.

That'll delete 10,000 of them at a time, for example.

You could also re-write the DELETE to use a subquery that uses TOP 10000 to also achive this effect.

Delete row when data is numeric?

I'm using a DTS package to import a large CSV file. There is a particular column that contains text or numbers. I want to delete the row if that column has a number, I've used IsNumeric in the selection portion of the statement, but can't figure out how to use it as part of my where clause.Never mind - i got it right after I posted... it has been a long week and I'm not thinking clearly any longer: Where IsNumeric(columnName)=1

Delete row from MS SQL Server 2000

Hi, I'm using SQL Server 2000 Personal Edition. I have created a table 'Student' which has 2 fields.

ID -- Varchar(10), Primary Key - contains ID of a student

NAME -- Varchar(50) -- contains names of student

ID NAME

0107200701 abcd

0107200702 cdgdh

0107200703 iyiylklk

.

.

I want to delete a complete row (all entries) from the "student" table using/specifing 'ID'. What will be the SQL query for that?

Hi

Try to use this (and please read help )

delete from Student where [ID] = '0107200701'

You should change the ID to your real ID

Regards,

Janos

Delete records each 15 day

Hi I have a table contains shopping cart info I want to delete the carts who
are 15 day old I have a coloumn that I store date in that ,
how can I delete records each 15 day and can I do it dynamicly?
Regards mahsaTry:
delete MyTable
where
TheDate < getdate () - 15
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"mahsa" <anonymous@.discussions.microsoft.com> wrote in message
news:6AF95014-D4BE-4064-8E18-7B3FB89FA3E7@.microsoft.com...
Hi I have a table contains shopping cart info I want to delete the carts who
are 15 day old I have a coloumn that I store date in that ,
how can I delete records each 15 day and can I do it dynamicly?
Regards mahsa|||Yes, write a stored procedure like this:
CREATE PROCEDURE dbo.clearCartData
AS
BEGIN
DELETE CartTable
WHERE [date column] < GETDATE()-15
END
GO
Then use SQL Agent to schedule the job to run once per day. (I assume you
want a rolling window, rather than delete a bunch of data every 15 days.)
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"mahsa" <anonymous@.discussions.microsoft.com> wrote in message
news:6AF95014-D4BE-4064-8E18-7B3FB89FA3E7@.microsoft.com...
> Hi I have a table contains shopping cart info I want to delete the carts
> who are 15 day old I have a coloumn that I store date in that ,
> how can I delete records each 15 day and can I do it dynamicly?
> Regards mahsa
>

Delete records each 15 day

Hi I have a table contains shopping cart info I want to delete the carts who are 15 day old I have a coloumn that I store date in that ,
how can I delete records each 15 day and can I do it dynamicly?
Regards mahsa
Try:
delete MyTable
where
TheDate < getdate () - 15
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"mahsa" <anonymous@.discussions.microsoft.com> wrote in message
news:6AF95014-D4BE-4064-8E18-7B3FB89FA3E7@.microsoft.com...
Hi I have a table contains shopping cart info I want to delete the carts who
are 15 day old I have a coloumn that I store date in that ,
how can I delete records each 15 day and can I do it dynamicly?
Regards mahsa
|||Yes, write a stored procedure like this:
CREATE PROCEDURE dbo.clearCartData
AS
BEGIN
DELETE CartTable
WHERE [date column] < GETDATE()-15
END
GO
Then use SQL Agent to schedule the job to run once per day. (I assume you
want a rolling window, rather than delete a bunch of data every 15 days.)
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"mahsa" <anonymous@.discussions.microsoft.com> wrote in message
news:6AF95014-D4BE-4064-8E18-7B3FB89FA3E7@.microsoft.com...
> Hi I have a table contains shopping cart info I want to delete the carts
> who are 15 day old I have a coloumn that I store date in that ,
> how can I delete records each 15 day and can I do it dynamicly?
> Regards mahsa
>

Delete records each 15 day

Hi I have a table contains shopping cart info I want to delete the carts who are 15 day old I have a coloumn that I store date in that
how can I delete records each 15 day and can I do it dynamicly
Regards mahsTry:
delete MyTable
where
TheDate < getdate () - 15
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"mahsa" <anonymous@.discussions.microsoft.com> wrote in message
news:6AF95014-D4BE-4064-8E18-7B3FB89FA3E7@.microsoft.com...
Hi I have a table contains shopping cart info I want to delete the carts who
are 15 day old I have a coloumn that I store date in that ,
how can I delete records each 15 day and can I do it dynamicly?
Regards mahsa|||Yes, write a stored procedure like this:
CREATE PROCEDURE dbo.clearCartData
AS
BEGIN
DELETE CartTable
WHERE [date column] < GETDATE()-15
END
GO
Then use SQL Agent to schedule the job to run once per day. (I assume you
want a rolling window, rather than delete a bunch of data every 15 days.)
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"mahsa" <anonymous@.discussions.microsoft.com> wrote in message
news:6AF95014-D4BE-4064-8E18-7B3FB89FA3E7@.microsoft.com...
> Hi I have a table contains shopping cart info I want to delete the carts
> who are 15 day old I have a coloumn that I store date in that ,
> how can I delete records each 15 day and can I do it dynamicly?
> Regards mahsa
>

Delete record based on existence of another record in same table?

Hi All,

I have a table in SQL Server 2000 that contains several million member
ids. Some of these member ids are duplicated in the table, and each
record is tagged with a 1 or a 2 in [recsrc] to indicate where they
came from.

I want to remove all member ids records from the table that have a
recsrc of 1 where the same member id also exists in the table with a
recsrc of 2.

So, if the member id has a recsrc of 1, and no other record exists in
the table with the same member id and a recsrc of 2, I want it left
untouched.

So, in a theortetical dataset of member id and recsrc:

0001, 1
0002, 2
0001, 2
0003, 1
0004, 2

I am looking to only delete the first record, because it has a recsrc
of 1 and there is another record in the table with the same member id
and a recsrc of 2.

I'd very much appreciate it if someone could help me achieve this!

Much warmth,

Murray"M Wells" <planetquirky@.planetthoughtful.org> wrote in message
news:io7r60hup1ajlnu3je9onmb7ki2dtkmni3@.4ax.com...
> Hi All,
> I have a table in SQL Server 2000 that contains several million member
> ids. Some of these member ids are duplicated in the table, and each
> record is tagged with a 1 or a 2 in [recsrc] to indicate where they
> came from.
> I want to remove all member ids records from the table that have a
> recsrc of 1 where the same member id also exists in the table with a
> recsrc of 2.
> So, if the member id has a recsrc of 1, and no other record exists in
> the table with the same member id and a recsrc of 2, I want it left
> untouched.
> So, in a theortetical dataset of member id and recsrc:
> 0001, 1
> 0002, 2
> 0001, 2
> 0003, 1
> 0004, 2
> I am looking to only delete the first record, because it has a recsrc
> of 1 and there is another record in the table with the same member id
> and a recsrc of 2.
> I'd very much appreciate it if someone could help me achieve this!
> Much warmth,
> Murray

I think this is what you're looking for:

delete from dbo.MyTable
where recsrc = 1
and exists (
select * from dbo.MyTable m2
where MyTable.MemberID = m2.MemberID
and m2.recsrc = 2)

Simon|||On Fri, 02 Apr 2004 17:19:29 GMT, M Wells
<planetquirky@.planetthoughtful.org> wrote:

And just to show that I am trying, I attempted:

delete from #mw_dupetest as md where recsrc = 1 and exists (select mid
from #mw_dupetest where mid = md.mid and recsrc = 2)

This is obviously wrong, since I can't seem to assign a table alias in
a delete statement and I can't think of any other way of referring to
the mid column in the exists statement.

So, I'm hoping somone can help me understand how to do this the right
way.

Much warmth,

Murray|||On Fri, 2 Apr 2004 19:28:16 +0200, "Simon Hayes" <sql@.hayes.ch> wrote:

>> Murray
>I think this is what you're looking for:
>delete from dbo.MyTable
>where recsrc = 1
>and exists (
>select * from dbo.MyTable m2
>where MyTable.MemberID = m2.MemberID
>and m2.recsrc = 2)

Hi Simon,

Thank you for this! Seems like I was somewhat on the right track, I
just fudged on attempting to alias the table in the delete statement.

Thanks again!

Much warmth,

Murray