deleting all rows in table which references itself

Using SQL Server 2000.
The example code below shows a
table which references itself.
i.e. column "parent" references column "pk" :
==
create table table1 (pk int not null primary key, parent int null,
aname varchar(50) null)
alter table table1 add constraint fk_table1_table1
foreign key (parent) references table1 (pk)
go
insert table1 values (1, null, 'one')
insert table1 values (11, 1, 'one-one')
go
delete from table1 -- XX
==
Say I want to delete all rows from the table,
as at the line marked at "XX". Is the
SQL server processing of the delete
statement, such that the delete statement
will always succeed, or can I actually
get a constraint violation in the above example ?
I'm not seeing a constraint violation, but I'm not
sure if this is by luck, or by design.
TIA,
StephenStephen
You can get an error only if you will provide WHERE condition such as
delete from table1 where pk=1
If you can explain what are you trying to do , so it will be more easier to
came up with a solution.
"Stephen Ahn" <noaddress_at_noaddress.com> wrote in message
news:OLFHs3YNFHA.1732@.TK2MSFTNGP14.phx.gbl...
> Using SQL Server 2000.
> The example code below shows a
> table which references itself.
> i.e. column "parent" references column "pk" :
> ==
> create table table1 (pk int not null primary key, parent int null,
> aname varchar(50) null)
> alter table table1 add constraint fk_table1_table1
> foreign key (parent) references table1 (pk)
> go
> insert table1 values (1, null, 'one')
> insert table1 values (11, 1, 'one-one')
> go
> delete from table1 -- XX
> ==
>
> Say I want to delete all rows from the table,
> as at the line marked at "XX". Is the
> SQL server processing of the delete
> statement, such that the delete statement
> will always succeed, or can I actually
> get a constraint violation in the above example ?
> I'm not seeing a constraint violation, but I'm not
> sure if this is by luck, or by design.
>
> TIA,
> Stephen
>|||Uri,
What I was trying to work out was a safe way to delete all records in
tables which reference themselves, as in the example.
ie.
1) is it 100% safe to do : delete from table1,
("safe", as in : no constraint violation will ever be raised
in cases similar to the example, and all records will get
properly deleted). The table could also have many records
in reality.
OR
2) should I write a stored proc etc which deletes records
one by one, deleting child records first, then eventally
deleting the root nodes.
From your reply, it sounds like 1) is true.
Thanks,
Stephen
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uvY6iHbNFHA.2680@.TK2MSFTNGP09.phx.gbl...
> Stephen
> You can get an error only if you will provide WHERE condition such as
> delete from table1 where pk=1
> If you can explain what are you trying to do , so it will be more easier
> to
> came up with a solution.
>|||Stephen
Again, you are asking to different questions
Do you really want NO WHERE condition in your query?
I think the option 2 is right way to do that.
"Stephen Ahn" <noaddress_at_noaddress.com> wrote in message
news:euTJhZbNFHA.1732@.TK2MSFTNGP14.phx.gbl...
> Uri,
> What I was trying to work out was a safe way to delete all records in
> tables which reference themselves, as in the example.
> ie.
> 1) is it 100% safe to do : delete from table1,
> ("safe", as in : no constraint violation will ever be raised
> in cases similar to the example, and all records will get
> properly deleted). The table could also have many records
> in reality.
> OR
> 2) should I write a stored proc etc which deletes records
> one by one, deleting child records first, then eventally
> deleting the root nodes.
> From your reply, it sounds like 1) is true.
> Thanks,
> Stephen
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uvY6iHbNFHA.2680@.TK2MSFTNGP09.phx.gbl...
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%230IbsOcNFHA.3772@.TK2MSFTNGP15.phx.gbl...
> Do you really want NO WHERE condition in your query?
Yes, this is a special case where we actually want to clean out the table
completely.
Thanks,
Stephen|||DELETE FROM Table1
is "safe" in the sense that it will not cause any violations of the
self-referencing foreign key. Constraints are validated against the
final result of a DML operation (except in the special case where you
use user-defined functions in a constraint and the function references
other rows in the same table).
David Portas
SQL Server MVP
--

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]

...

)

delete rows using except ?

Found out that except is very fast, but is it possible to delete rows using except ?

Like:

delete accountnumber, central
from table1
except
select accountnumber, central
from table 2

Jam:

The DELETE FROM ... WHERE NOT EXISTS is likely to be faster than a DELETE FROM ... FROM ... EXCEPT; something such as:

delete from table1
from table1 a
where not exists
( select 0 /* a dummy */ from table2 b
where a.accountNumber = b.accountNumber
and a.central = b.central
)

Dave

|||

Hmmmm. This query seems to have the same amount of logical IO as the previous query:

delete from table1
from table1 a
inner join
( select accountNumber,
central
from table1
except
select accountNumber,
central
from table2
) b
on a.accountNumber = b.accountNumber
and a.central = b.central

I'll do a little more digging. At the moment, this query also looks viable.

Dave

|||

Sorry about that, I was simultaneously dealing with an implementation problem. Really, I cannot go any farther with my mock-up without knowing specifics about your actual implementation because your implementation will have a big impact on the execution plan. Yeah, I tested out with the same amount of logical IOs with both queries but this really is more qualitative than anything and doesn't prove too much.

I can say little more than the qualitative viability of the EXCEPT query; however, I don't consider this a simpler query than the NOT EXISTS query. Can you give more information about your two tables -- especially index information?

Dave

DELETE rows in MSDE

Hi..

DELETE FROM table1 WHERE projektID=5

there are 500000 rows that has projektID=5.. and when i run the query the hardrive is working for a couple of minutes and then stops. and NOTHING has happened. not a single row has been deleted?.. cant the DELETE statement handle that many rows or?. or is there another way i can delete these rows?.yeah, it can handle that. I've deleted more than 500,000 rows before (sometimes by accident!)

DELETE table1 WHERE projektID = 5

are you running this from query analyzer or from ASP.NET?|||im running it from asp.net, there is no query analyser for MSDE.
but it doesnt happen anything when do it, except for my harddrive gets occupied for 10 min or so.|||> there is no query analyser for MSDE.

sort of. MSDE being the SQL engine, you can connect to it with SQL Server client tools, and many people do.

do me a favour. install the 180 day SQL Server trial - client tools only. then run it through QA

Delete records from several tables at once

Hi!
Is there any SQL command that allow me to delete records from a list of
tables?
Something like this: "Delete From Table1, Table2"
TIA
Jorge
JotaO wrote:
> Hi!
> Is there any SQL command that allow me to delete records from a list of
> tables?
> Something like this: "Delete From Table1, Table2"
> TIA
> Jorge
Answered in microsoft.public.sqlserver.programming
Please do not multi-post!
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Delete records from several tables at once

Hi!
Is there any SQL command that allow me to delete records from a list of
tables?
Something like this: "Delete From Table1, Table2"
TIA
JorgeJotaO wrote:
> Hi!
> Is there any SQL command that allow me to delete records from a list of
> tables?
> Something like this: "Delete From Table1, Table2"
> TIA
> Jorge
Answered in microsoft.public.sqlserver.programming
Please do not multi-post!
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Delete records from several tables at once

Hi!
Is there any SQL command that allow me to delete records from a list of
tables?
Something like this: "Delete From Table1, Table2"
TIA
JorgeJotaO wrote:
> Hi!
> Is there any SQL command that allow me to delete records from a list of
> tables?
> Something like this: "Delete From Table1, Table2"
> TIA
> Jorge
Answered in microsoft.public.sqlserver.programming
Please do not multi-post!
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Delete records from serveral tables at once

Hi!
Is there any SQL command that allow me to delete records from a list of
tables?
Something like this: "Delete From Table1, Table2"
TIA
JorgeNo. However, it's possible to speed up the process by using TRUNCATE
TABLE - as long as no foreign keys refer to these tables.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"JotaO" <antispam@.antispam.com> wrote in message
news:%23YKsGzLUGHA.1444@.TK2MSFTNGP11.phx.gbl...
Hi!
Is there any SQL command that allow me to delete records from a list of
tables?
Something like this: "Delete From Table1, Table2"
TIA
Jorge|||Hi
If there is a foreign key in Table2 referencing Table1 then you can enable
cascading deletes.
John
"JotaO" <antispam@.antispam.com> wrote in message
news:%23YKsGzLUGHA.1444@.TK2MSFTNGP11.phx.gbl...
> Hi!
> Is there any SQL command that allow me to delete records from a list of
> tables?
> Something like this: "Delete From Table1, Table2"
> TIA
> Jorge
>

delete record does not exist in table

I want to delete records in table1 if account number does not exist in table
2.
I have following code:
DELETE FROM TABLE1
WHERE ACCOUNT_NUMBER not in(SELECT ACCOUNT_NUMBER FROM TABLE2)
Should the above code work?
Any informaion is great appreciated,Yes, that should work just fine...
DELETE FROM TABLE1
WHERE ACCOUNT_NUMBER not in
(SELECT ACCOUNT_NUMBER FROM TABLE2)
You can also use Not Exists
DELETE TABLE1 T1
WHERE Not Exists
(SELECT * FROM TABLE2
Where ACCOUNT_NUMBER =
T1.ACCOUNT_NUMBER)
"Souris" wrote:

> I want to delete records in table1 if account number does not exist in tab
le2.
> I have following code:
> DELETE FROM TABLE1
> WHERE ACCOUNT_NUMBER not in(SELECT ACCOUNT_NUMBER FROM TABLE2)
> Should the above code work?
> Any informaion is great appreciated,
>|||To add to CBretana's response, your NOT IN construct will work as expected
unless there is a NULL ACCOUNT_NUMBER in TABLE2. No rows will be returned
when there are one or more NULL values. Personally, I prefer NOT EXISTS
because that produces the behavior desired in most situations.
Hope this helps.
Dan Guzman
SQL Server MVP
"Souris" <Souris@.discussions.microsoft.com> wrote in message
news:DF0FDD5A-FC29-4120-8D09-D070F5E43520@.microsoft.com...
>I want to delete records in table1 if account number does not exist in
>table2.
> I have following code:
> DELETE FROM TABLE1
> WHERE ACCOUNT_NUMBER not in(SELECT ACCOUNT_NUMBER FROM TABLE2)
> Should the above code work?
> Any informaion is great appreciated,
>|||Dan
I agree , but if you change 'a little bit :-)' his query it should work as
well as NOT EXISTS .
People just forget with NOT IN to add WHERE condition with an outer table.
DELETE FROM TABLE1
WHERE ACCOUNT_NUMBER not in
(SELECT ACCOUNT_NUMBER FROM TABLE2 Where TABLE1.ACCOUNT_NUMBER =
TABLE2 .ACCOUNT_NUMBER)
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OlRMglaRFHA.252@.TK2MSFTNGP12.phx.gbl...
> To add to CBretana's response, your NOT IN construct will work as expected
> unless there is a NULL ACCOUNT_NUMBER in TABLE2. No rows will be returned
> when there are one or more NULL values. Personally, I prefer NOT EXISTS
> because that produces the behavior desired in most situations.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Souris" <Souris@.discussions.microsoft.com> wrote in message
> news:DF0FDD5A-FC29-4120-8D09-D070F5E43520@.microsoft.com...
>

DELETE not deleting all records..mystery.

I've got a job that has 3 lines and it reports success after it runs.
DELETE FROM TABLE1 WHERE DATE >= '20070115'
DELETE FROM TABLE2 WHERE DATE >= '20070115'
DELETE FROM TABLE3 WHERE DATE >= '20070115'
I then have a separate job for each line below which imports from another
server using DTS.
SELECT * FROM TABLE1 WHERE DATE >= '20070115'
SELECT * FROM TABLE2 WHERE DATE >= '20070115'
SELECT * FROM TABLE3 WHERE DATE >= '20070115'
The 1st job fails, but the other 2 are ok. I then use QA and execute the
single statement:
DELETE FROM TABLE1 WHERE DATE >= '20070115'
and several thousand records are deleted. huh?
During this process, there is no application or person inserting records
into the table.
How can there still be records in the table after the delete job ran? I then
rerun the job to insert from another server and it succeeds.
This has happened on many servers over the last few months. Any opinions
greatly appreciated.
Thanks,
Don
SQL 2000Hi
"donsql22222" wrote:
> I've got a job that has 3 lines and it reports success after it runs.
> DELETE FROM TABLE1 WHERE DATE >= '20070115'
> DELETE FROM TABLE2 WHERE DATE >= '20070115'
> DELETE FROM TABLE3 WHERE DATE >= '20070115'
> I then have a separate job for each line below which imports from another
> server using DTS.
> SELECT * FROM TABLE1 WHERE DATE >= '20070115'
> SELECT * FROM TABLE2 WHERE DATE >= '20070115'
> SELECT * FROM TABLE3 WHERE DATE >= '20070115'
> The 1st job fails, but the other 2 are ok. I then use QA and execute the
> single statement:
> DELETE FROM TABLE1 WHERE DATE >= '20070115'
> and several thousand records are deleted. huh?
> During this process, there is no application or person inserting records
> into the table.
> How can there still be records in the table after the delete job ran? I then
> rerun the job to insert from another server and it succeeds.
> This has happened on many servers over the last few months. Any opinions
> greatly appreciated.
> Thanks,
> Don
> SQL 2000
It would appear that when you say the first job to import data fails it is
not failing before data has been committed. If the package contains more than
a single Execute SQL task then it could be one of the subsequent steps that
fails (assuming they are not in a single transaction) or if the job has more
than one step it could be a subsequent step that has failed or the step has
already committed the data before it fails.
John

DELETE not deleting all records..mystery.

I've got a job that has 3 lines and it reports success after it runs.
DELETE FROM TABLE1 WHERE DATE >= '20070115'
DELETE FROM TABLE2 WHERE DATE >= '20070115'
DELETE FROM TABLE3 WHERE DATE >= '20070115'
I then have a separate job for each line below which imports from another
server using DTS.
SELECT * FROM TABLE1 WHERE DATE >= '20070115'
SELECT * FROM TABLE2 WHERE DATE >= '20070115'
SELECT * FROM TABLE3 WHERE DATE >= '20070115'
The 1st job fails, but the other 2 are ok. I then use QA and execute the
single statement:
DELETE FROM TABLE1 WHERE DATE >= '20070115'
and several thousand records are deleted. huh?
During this process, there is no application or person inserting records
into the table.
How can there still be records in the table after the delete job ran? I then
rerun the job to insert from another server and it succeeds.
This has happened on many servers over the last few months. Any opinions
greatly appreciated.
Thanks,
Don
SQL 2000
Hi
"donsql22222" wrote:

> I've got a job that has 3 lines and it reports success after it runs.
> DELETE FROM TABLE1 WHERE DATE >= '20070115'
> DELETE FROM TABLE2 WHERE DATE >= '20070115'
> DELETE FROM TABLE3 WHERE DATE >= '20070115'
> I then have a separate job for each line below which imports from another
> server using DTS.
> SELECT * FROM TABLE1 WHERE DATE >= '20070115'
> SELECT * FROM TABLE2 WHERE DATE >= '20070115'
> SELECT * FROM TABLE3 WHERE DATE >= '20070115'
> The 1st job fails, but the other 2 are ok. I then use QA and execute the
> single statement:
> DELETE FROM TABLE1 WHERE DATE >= '20070115'
> and several thousand records are deleted. huh?
> During this process, there is no application or person inserting records
> into the table.
> How can there still be records in the table after the delete job ran? I then
> rerun the job to insert from another server and it succeeds.
> This has happened on many servers over the last few months. Any opinions
> greatly appreciated.
> Thanks,
> Don
> SQL 2000
It would appear that when you say the first job to import data fails it is
not failing before data has been committed. If the package contains more than
a single Execute SQL task then it could be one of the subsequent steps that
fails (assuming they are not in a single transaction) or if the job has more
than one step it could be a subsequent step that has failed or the step has
already committed the data before it fails.
John

DELETE not deleting all records..mystery.

I've got a job that has 3 lines and it reports success after it runs.
DELETE FROM TABLE1 WHERE DATE >= '20070115'
DELETE FROM TABLE2 WHERE DATE >= '20070115'
DELETE FROM TABLE3 WHERE DATE >= '20070115'
I then have a separate job for each line below which imports from another
server using DTS.
SELECT * FROM TABLE1 WHERE DATE >= '20070115'
SELECT * FROM TABLE2 WHERE DATE >= '20070115'
SELECT * FROM TABLE3 WHERE DATE >= '20070115'
The 1st job fails, but the other 2 are ok. I then use QA and execute the
single statement:
DELETE FROM TABLE1 WHERE DATE >= '20070115'
and several thousand records are deleted. huh?
During this process, there is no application or person inserting records
into the table.
How can there still be records in the table after the delete job ran? I then
rerun the job to insert from another server and it succeeds.
This has happened on many servers over the last few months. Any opinions
greatly appreciated.
Thanks,
Don
SQL 2000Hi
"donsql22222" wrote:

> I've got a job that has 3 lines and it reports success after it runs.
> DELETE FROM TABLE1 WHERE DATE >= '20070115'
> DELETE FROM TABLE2 WHERE DATE >= '20070115'
> DELETE FROM TABLE3 WHERE DATE >= '20070115'
> I then have a separate job for each line below which imports from another
> server using DTS.
> SELECT * FROM TABLE1 WHERE DATE >= '20070115'
> SELECT * FROM TABLE2 WHERE DATE >= '20070115'
> SELECT * FROM TABLE3 WHERE DATE >= '20070115'
> The 1st job fails, but the other 2 are ok. I then use QA and execute the
> single statement:
> DELETE FROM TABLE1 WHERE DATE >= '20070115'
> and several thousand records are deleted. huh?
> During this process, there is no application or person inserting records
> into the table.
> How can there still be records in the table after the delete job ran? I th
en
> rerun the job to insert from another server and it succeeds.
> This has happened on many servers over the last few months. Any opinions
> greatly appreciated.
> Thanks,
> Don
> SQL 2000
It would appear that when you say the first job to import data fails it is
not failing before data has been committed. If the package contains more tha
n
a single Execute SQL task then it could be one of the subsequent steps that
fails (assuming they are not in a single transaction) or if the job has more
than one step it could be a subsequent step that has failed or the step has
already committed the data before it fails.
John