Deleteing duplicate records from my table

I just discovered that all my records appear twice inside my table, in
other words, they repeat on the row below. How can I delete all of the
duplicates? I'm sure there must be a tidy line of sql to do that.

Thanks,

Billbillzimmerman@.gospellight.com (Bill) wrote in message news:<8da5f4f4.0307310856.79830a8e@.posting.google.com>...
> I just discovered that all my records appear twice inside my table, in
> other words, they repeat on the row below. How can I delete all of the
> duplicates? I'm sure there must be a tidy line of sql to do that.
> Thanks,
> Bill

Maybe run a select distinct query and insert the results into a new
table? If need be, you could then delete all records from your
original table and insert the records back.

That should be reasonably tidy but it is hard to say what the
performance would look like without knowing the specifics.

delete table1 from openxml

Hi,
I know how to select using openxml.
Could you please let me have a sample code on how to delete records from a table based on what is inside an xml.
Thanks

Something like:
delete from table1
where
OPENXML (@.idoc, '/marketData/forwardCurves/forwardCurve/maturities/maturity',2)
WITH (
contractMonth varchar(50),
[date] datetime,
mid varchar(50),
bizDays int
)

There are a couple of ways to approach this. One of the easiest ways (again easy but not necessarily the highest performance) is to use a JOIN.

Example :

delete from table1 FROM table1 JOIN
SELECT contractMonth, [date], mid, bizdays

OPENXML (@.idoc, '/marketData/forwardCurves/forwardCurve/maturities/maturity',2)
WITH (
contractMonth varchar(50),
[date] datetime,
mid varchar(50),
bizDays int
)) example1 on table1."commonkey" = example1."commonkey"

|||

You can use OPENXML in FROM clause so do below:

delete from table1

where exists (

select *

from OPENXML(...)

with (...) as x

where x.contractMonth = table1.contractMonth

and x.[date] = table.[date]

...

)

delete table1 from openxml

Hi,
I know how to select using openxml.
Could you please let me have a sample code on how to delete records from a table based on what is inside an xml.
Thanks

Something like:
delete from table1
where
OPENXML (@.idoc, '/marketData/forwardCurves/forwardCurve/maturities/maturity',2)
WITH (
contractMonth varchar(50),
[date] datetime,
mid varchar(50),
bizDays int
)

There are a couple of ways to approach this. One of the easiest ways (again easy but not necessarily the highest performance) is to use a JOIN.

Example :

delete from table1 FROM table1 JOIN
SELECT contractMonth, [date], mid, bizdays

OPENXML (@.idoc, '/marketData/forwardCurves/forwardCurve/maturities/maturity',2)
WITH (
contractMonth varchar(50),
[date] datetime,
mid varchar(50),
bizDays int
)) example1 on table1."commonkey" = example1."commonkey"

|||

You can use OPENXML in FROM clause so do below:

delete from table1

where exists (

select *

from OPENXML(...)

with (...) as x

where x.contractMonth = table1.contractMonth

and x.[date] = table.[date]

...

)