Delete Data

Simple question, what is that I should use to delete data from one table and
keep the table's definition? DROP TABLE deletes everything and I need to
maintain the table's definition.
Thanks a lot.
TSYou can use "truncate table table_name" or "delete table_name". The first on
e
is not a logged operation.
AMB
"TS" wrote:

> Simple question, what is that I should use to delete data from one table a
nd
> keep the table's definition? DROP TABLE deletes everything and I need to
> maintain the table's definition.
> Thanks a lot.
> --
> TS|||In order to do a truncate table, there must be no foreign keys...
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"TS" <TS@.discussions.microsoft.com> wrote in message
news:A95E62C6-A8C1-4590-B71C-DE8726761C9F@.microsoft.com...
> Simple question, what is that I should use to delete data from one table
> and
> keep the table's definition? DROP TABLE deletes everything and I need to
> maintain the table's definition.
> Thanks a lot.
> --
> TS|||If there are foreign keys pointing to the table, you need to clean those up
first.
If there are foreign keys in the table pointing elsewhere, you need to say
DELETE tablename
Otherwise you can use
TRUNCATE TABLE tablename
(Which also resets the IDENTITY seed if such a column exists.)
"TS" <TS@.discussions.microsoft.com> wrote in message
news:A95E62C6-A8C1-4590-B71C-DE8726761C9F@.microsoft.com...
> Simple question, what is that I should use to delete data from one table
> and
> keep the table's definition? DROP TABLE deletes everything and I need to
> maintain the table's definition.
> Thanks a lot.
> --
> TSsql

delete contraint

HI all,
Hope I have the correct terminology.
I have related tables, with cascading deletes off. In otherwords, you cannot
delete a parent row if there are related child row.
Now when this happens a correct error is produced.
Is there any way to have a single return statement stipulation which child
table was involved in the cascading delete( or rarther the parent delete
failing)
Reasoning behind this is that because I have many child tables related with
a single parent, it would be a lot easier to know which table cause the
delete to fail, go to that table and determin if row in that particular
table can be deleted.
Currently I have to go to every related child table, and figure out if it
caused the delete to fail, and if ti did, then take the needed action.
I'm really looking for principals here, or for someone to steer me in the
right direction, perhaps some reading material etc.
Thanks
RobertAre you saying that you want to know which referencing table is it that proh
ibits the DELETE
operation? Check out the error message from the DELETE operation. If you hav
e several referencing
tables that has a conflict, you will only get on of them, though:
CREATE TABLE rd(c1 int primary key)
INSERT INTO rd (c1) VALUES(1)
GO
CREATE TABLE rs1(c1 int REFERENCES rd(c1))
CREATE TABLE rs2(c1 int REFERENCES rd(c1))
INSERT INTO rs2 (c1) VALUES(1)
CREATE TABLE rs3(c1 int REFERENCES rd(c1))
INSERT INTO rs3 (c1) VALUES(1)
GO
DELETE FROM rd WHERE c1 = 1
Server: Msg 547, Level 16, State 1, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK__rs2__c1__
4D0CD9BB". The conflict
occurred in database "tempdb", table "dbo.rs2", column 'c1'.
The statement has been terminated.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Robert Bravery" <me@.u.com> wrote in message news:egQxAm8MGHA.2828@.TK2MSFTNGP12.phx.gbl...[
color=darkred]
> HI all,
> Hope I have the correct terminology.
> I have related tables, with cascading deletes off. In otherwords, you cann
ot
> delete a parent row if there are related child row.
> Now when this happens a correct error is produced.
> Is there any way to have a single return statement stipulation which child
> table was involved in the cascading delete( or rarther the parent delete
> failing)
> Reasoning behind this is that because I have many child tables related wit
h
> a single parent, it would be a lot easier to know which table cause the
> delete to fail, go to that table and determin if row in that particular
> table can be deleted.
> Currently I have to go to every related child table, and figure out if it
> caused the delete to fail, and if ti did, then take the needed action.
> I'm really looking for principals here, or for someone to steer me in the
> right direction, perhaps some reading material etc.
> Thanks
> Robert
>[/color]

Delete - Exists - problem

Can anyone tell me why the following deletes all the records instead of
simply the ones returned in the "Exists" clause?
The select in the exists by itself returns 131 records, however when run in
the following context it deletes all 4474 that are in the Shades table.'
Delete FROM #TMP_SHADE WHERE EXISTS
(select DISTINCT(OLD_SHADE_ID) from #TMP_CHANGEDSHADES
where OLD_SHADE_ID NOT in (select SHADE_ID
from SHADE_SUC))
The idea is to delete any shade records in #tmp_shade where they do not
exist in shade_SUC
ThanksDan,

> Can anyone tell me why the following deletes all the records instead of
> simply the ones returned in the "Exists" clause?
Because you are not correlating the tables. If at least one row in table
#TMP_CHANGEDSHADES meet the condition, all rows from table #TMP_SHADE will b
e
deleted. It should be something like:
Delete
FROM #TMP_SHADE
WHERE EXISTS
(
select DISTINCT(OLD_SHADE_ID)
from #TMP_CHANGEDSHADES
where #TMP_CHANGEDSHADES.col1 = #TMP_SHADE.col1
and OLD_SHADE_ID NOT in (select SHADE_ID from SHADE_SUC)
)
AMB
"Dan" wrote:

> Can anyone tell me why the following deletes all the records instead of
> simply the ones returned in the "Exists" clause?
> The select in the exists by itself returns 131 records, however when run i
n
> the following context it deletes all 4474 that are in the Shades table.?
?
> Delete FROM #TMP_SHADE WHERE EXISTS
> (select DISTINCT(OLD_SHADE_ID) from #TMP_CHANGEDSHADES
> where OLD_SHADE_ID NOT in (select SHADE_ID
> from SHADE_SUC))
> The idea is to delete any shade records in #tmp_shade where they do not
> exist in shade_SUC
> Thanks
>|||Delete from X where Exists (Y)
will delete all rows in X if Y is true and nothing if Y is false.
try something like
Delete from X where Y_id in (select distinct(Y_id) from Y)
I hope you get the idea.
Regards,
Nishant
"Dan" wrote:

> Can anyone tell me why the following deletes all the records instead of
> simply the ones returned in the "Exists" clause?
> The select in the exists by itself returns 131 records, however when run i
n
> the following context it deletes all 4474 that are in the Shades table.?
?
> Delete FROM #TMP_SHADE WHERE EXISTS
> (select DISTINCT(OLD_SHADE_ID) from #TMP_CHANGEDSHADES
> where OLD_SHADE_ID NOT in (select SHADE_ID
> from SHADE_SUC))
> The idea is to delete any shade records in #tmp_shade where they do not
> exist in shade_SUC
> Thanks
>|||Great Alejandro! - Thank you for the pointer
Dan
"Alejandro Mesa" wrote:
> Dan,
>
> Because you are not correlating the tables. If at least one row in table
> #TMP_CHANGEDSHADES meet the condition, all rows from table #TMP_SHADE will
be
> deleted. It should be something like:
> Delete
> FROM #TMP_SHADE
> WHERE EXISTS
> (
> select DISTINCT(OLD_SHADE_ID)
> from #TMP_CHANGEDSHADES
> where #TMP_CHANGEDSHADES.col1 = #TMP_SHADE.col1
> and OLD_SHADE_ID NOT in (select SHADE_ID from SHADE_SUC)
> )
>
> AMB
>
>
> "Dan" wrote:
>sql

Define more than one relationship per table?

Why is it not possible to define more than one relationship per table?

i have a primary table that i would like to cascade deletes to 2 other foreign tables in 2 separate relationships. why can't i do this and what are my alternatives?

thank you::Why is it not possible to define more than one relationship per table?

This is possible. You are in error here. read the error message.

::i have a primary table that i would like to cascade deletes to 2 other foreign tables in 2
::separate relationships.

THIS is not possible. You can only have one cascade.

::why can't i do this

Wrong location for this question. Ask the developers.

::and what are my alternatives?

Use a trigger.|||thank you for your response,

in my attempts to use a trigger, i can't seem to get around the error of a subquery returning more than one result. how can you implement a cascading delete with this restriction and/or how do your work around it?

thank you|||Triggers.

And get used to set based operation.

::i can't seem to get around the error of a subquery returning more than one result

What subquery?|||'subquery' refers to the DELETE query in my trigger.

more specifically, i have a DELETE trigger in a primary table. in this DELETE trigger, i have a DELETE query that deletes records in a foreign 'many' table. it is this DELETE query that is the 'subquery' in the error message as listed below;

error message
===============
Server: Msg 512, Level 16, State 1, Procedure triggerMyPrimaryTableDelete, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
===============

trigger code
===============
CREATE TRIGGER triggerMyPrimaryTableDelete
ON dbo.MyPrimaryTable FOR DELETE
AS
BEGIN
DELETE MyForeignManyTable
FROM MyForeignManyTable , deleted
WHERE MyForeignManyTable.ID = deleted.ID
END
===============

thank you