Deleting records from multiple tables in SQL server

I'm new to relational database concepts and designs, but what i've learned so far has been helpful. I now know how to select certain records from multiple tables using joins, etc. Now I need info on how to do complete deletes. I've tried reading articles on cascading deletes, but the people writing them are so verbose that they are confusing to understand for a beginner. I hope someone could help me with this problem.

I have sql server 2005. I use visual studio 2005. In the database I've created the following tables(with their column names):

Table 1: Classes --Columns: ClassID, ClassName

Table 2: Roster--Columns: ClassID, StudentID, Student Name

Table 3: Assignments--Columns: ClassID, AssignmentID, AssignmentName

Table 4: Scores--StudentID, AssignmentID, Score

What I can't seem to figure out is how can I delete a class (ClassID) from Classes and as a result of this one deletion, delete all students in the Roster table associated with that class, delete all assignments associated with that class, delete all scores associated with all assignments associated with that class in one DELETE sql statement.

What I tried to do in sql server management studio is set the ClassID in Classes as a primary key, then set foreign keys to the other three tables. However, also set AssignmentID in Table 4 as a foreign key to Table 3.

The stored procedure I created was

DELETE FROM Classes WHERE ClassID=@.classid

I thought, since I established ClassID as a primary key in Classes, that by deleting it, it would also delete all other rows in the foreign tables that have the same value in their ClassID columns. But I get errors when I run the query. The error said:

The DELETE statement conflicted with the REFERENCE constraint "FK_Roster_Classes1". The conflict occurred in database "database", table "dbo.Roster", column 'ClassID'.
The statement has been terminated.

What are reference constraints? What are they talking about? Plus is the query correct? If not, how would I go about solving my problem. Would I have to do joins while deleting?

I thought I was doing a cascade delete. The articles I read kept insisting that cascade deletes are deletes where if you delete a record from a parent table, then the rows in the child table will also be deleted, but I get the error.

Did I approach this right? If not, please show me how, and please, please explain it like I'm a four year old.

Further, is there something else I need to do besides assigning primary keys and foreign keys?

WHen you create a foreign key, there are some additional options you have to set to tell it to do the cascade delete.

If you are creating the foreign key through T-SQL you must append the ON DELETE CASCADE option to the foreign key:

Code Snippet

ALTER TABLE <tablename>
ADD CONSTRAINT <constraintname> FOREIGN KEY (<columnname(s)>)
REFERENCES <referencedtablename> (<columnname(s)>)

ON DELETE CASCADE;

If using SSMS, modify the table and go to where you created the relationship. If you look around in there you should see an option to set the "Delete Rule" Set that to CASCADE.

|||

OK, the concept of deleting rows from multiple tables in a single delete statement cannot be done in just that statement. There is the concept of triggers on the tables that do deletes in a cascading style, but I would not recommend you do it that way for sake of control of the actions of the data.

But I udnerstand what you want to do, and the best way to explain it is this:

Say you have these tables and each one has a relationship up the chain. If you have Classes, and they are on Rosters, and Assignments are given to Classes and Scores have Students and Assignments you have to see which is the last one in the chain.

So in your case you have Classes that is the base and then you have Classes in Rosters (this is the next level) and you have Classes in Assignments (same level as Rosters).

So you have a relationship like

Classes - ClassID

|__Roster - ClassId

|__Assignments - ClassId

|Scores - AssignmentId

So say you no longer had ClassId 3 and you wanted to just get rid of all the records that are associated with ClassId 3. Here are the steps that you would need to take. You would delete in the reverse order than you inserted.

So in this case, you would want to use the ClassId = 3 to get all the assignments that have that ClassId and delete the Scores that have the AssignmentId and then delete the Assignments with the ClassId = 3

Then you would delete the Rosters with the ClassId = 3 and then finally delete the Classes with ClassId = 3

SQL:

DELETE Scores

FROM Assignments A

INNER JOIN Scores S ON A.AssignmentId = S.AssignmentId

WHERE A.ClassId = 3

DELETE Assignments

WHERE ClassId = 3

DELETE Roster

WHERE ClassId = 3

DELETE Classes

WHERE ClassId = 3

So you really just need to delete the Foreign Key tables records with the Primary Key record in it first and then delete the Primary Key records in the Primary table or Base table last.

HTH.

Ben Miller

|||I disagree with you an the answer that this cannot be done within one statement as the suggestions from Andrew about Cascading deletes should solve the problem, if the architecture is appropiate for the original poster.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Ben

I also disagree with your presentation. With a properly established set of relationships, CASCADE DELETE works wonderfully. The PK-FK relationships must be properly set-up, and there cannot be any circular relationships.

So you have a relationship like

Classes - ClassID

|__Roster - ClassId

|__Assignments - ClassId

|Scores - AssignmentId

In this situation, a deletion on [Classes] will remove related data from all lower tables. Deleting [Assignments] will also delete related data from [Scores]. A deletion on [Roster] or [Scores] will only affect those tables.

deleting multiple tables through the Management Studio GUI

is there a way to delete multiple tables using the Management Studio GUI?In the Object Explorer Detail pane, while holding down the [ctrl] key, select the tables to delete, and the press the [Delete] key.

deleting multiple packages at once

Hi,

How do you delete multiple packages at once ?

We have a folder in SSIS called ETL and there are about 25 SSIS packages in it.

Now we need to update it.

I tried to delete the folder but you get the message that the folder is not empty.

So I renamed the folder to ETL_old and my deployement works fine.

But now I want to get rid of all the old folders. Delete a folder didn't work.

Selecting multiple packages doesn't work.

To delete a single one you select the package, right click and select delete, click yes.

But then I have to do it 25 times. I was unable to set a short cut with the keyboard for that action.

Any ideas ?

Constantijn Enders

I don't think you can. But if you find a way please post it

cheers

sql

deleting multiple databases on 2000

Hi,
I need to delete approx 50 db on the same server. Instead of deleting one
at a time, is there a better way? Is there a script that someone can suggest
many thx
Assuming you have only a couple of database that you want to keep, you could
create a script that uses a cursor to to select the databases
(master..sysdatabases) that you want to keep and then dynamically drop
eveything that is 'not in' your selected databases to keep.
Be warned!!! - make sure you eliminate the system databases.
If it were me, i'd just drop them manually just to be safe!
Immy
p.s. Ensure you have them all backed up! ;)
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:B860DA2F-705A-446C-AEC0-11B16350F3DB@.microsoft.com...
> Hi,
> I need to delete approx 50 db on the same server. Instead of deleting one
> at a time, is there a better way? Is there a script that someone can
> suggest
> many thx
|||you could also use the ms undocumented command sp_foreachDB
but would still need to check for system databases first.
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:%233QpAlPVHHA.192@.TK2MSFTNGP04.phx.gbl...
> Assuming you have only a couple of database that you want to keep, you
> could create a script that uses a cursor to to select the databases
> (master..sysdatabases) that you want to keep and then dynamically drop
> eveything that is 'not in' your selected databases to keep.
> Be warned!!! - make sure you eliminate the system databases.
> If it were me, i'd just drop them manually just to be safe!
> Immy
> p.s. Ensure you have them all backed up! ;)
>
> "stoney" <stoney@.discussions.microsoft.com> wrote in message
> news:B860DA2F-705A-446C-AEC0-11B16350F3DB@.microsoft.com...
>
|||Hi,
Please find the script for the same:
------
--Name : s_RebuildIndices
--Author : Pallavi
--Description: Rebuilds all table indices
--Notes:
--Date: 28 March 2006
Create PROCEDURE dbo.s_DropUserdatabases
AS
SET NOCOUNT ON
-- declare all variables
DECLARE @.sTableName SYSNAME
DECLARE @.sSQL VARCHAR(50)
DECLARE @.iRowCount INT
DECLARE @.t_TableNames_Temp TABLE
(table_name SYSNAME)
INSERT @.t_TableNames_Temp
SELECT name
FROM SYSDATABASES
WHERE name not in ('master','msdb','model','tempdb')
ORDER BY name
--Getting row count from table
SELECT @.iRowCount = COUNT(*) FROM @.t_TableNames_Temp
WHILE @.iRowCount > 0
BEGIN
SELECT @.sTableName = table_name from @.t_TableNames_Temp
SELECT @.sSQL = 'DROP DATABASE '+@.sTableName
EXEC (@.sSQL)
DELETE FROM @.t_TableNames_Temp WHERE @.sTableName = table_name
SELECT @.iRowCount = @.iRowCount - 1
END
RETURN 0
SET NOCOUNT OFF
GO
"Mark Broadbent" wrote:

> you could also use the ms undocumented command sp_foreachDB
> but would still need to check for system databases first.
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:%233QpAlPVHHA.192@.TK2MSFTNGP04.phx.gbl...
>
>

deleting multiple databases on 2000

Hi,
I need to delete approx 50 db on the same server. Instead of deleting one
at a time, is there a better way? Is there a script that someone can sugges
t
many thxAssuming you have only a couple of database that you want to keep, you could
create a script that uses a cursor to to select the databases
(master..sysdatabases) that you want to keep and then dynamically drop
eveything that is 'not in' your selected databases to keep.
Be warned!!! - make sure you eliminate the system databases.
If it were me, i'd just drop them manually just to be safe!
Immy
p.s. Ensure you have them all backed up! ;)
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:B860DA2F-705A-446C-AEC0-11B16350F3DB@.microsoft.com...
> Hi,
> I need to delete approx 50 db on the same server. Instead of deleting one
> at a time, is there a better way? Is there a script that someone can
> suggest
> many thx|||you could also use the ms undocumented command sp_foreachDB
but would still need to check for system databases first.
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:%233QpAlPVHHA.192@.TK2MSFTNGP04.phx.gbl...
> Assuming you have only a couple of database that you want to keep, you
> could create a script that uses a cursor to to select the databases
> (master..sysdatabases) that you want to keep and then dynamically drop
> eveything that is 'not in' your selected databases to keep.
> Be warned!!! - make sure you eliminate the system databases.
> If it were me, i'd just drop them manually just to be safe!
> Immy
> p.s. Ensure you have them all backed up! ;)
>
> "stoney" <stoney@.discussions.microsoft.com> wrote in message
> news:B860DA2F-705A-446C-AEC0-11B16350F3DB@.microsoft.com...
>|||Hi,
Please find the script for the same:
----
--
--Name : s_RebuildIndices
--Author : Pallavi
--Description : Rebuilds all table indices
--Notes :
--Date : 28 March 2006
Create PROCEDURE dbo.s_DropUserdatabases
AS
SET NOCOUNT ON
-- declare all variables
DECLARE @.sTableName SYSNAME
DECLARE @.sSQL VARCHAR(50)
DECLARE @.iRowCount INT
DECLARE @.t_TableNames_Temp TABLE
(table_name SYSNAME)
INSERT @.t_TableNames_Temp
SELECT name
FROM SYSDATABASES
WHERE name not in ('master','msdb','model','tempdb')
ORDER BY name
--Getting row count from table
SELECT @.iRowCount = COUNT(*) FROM @.t_TableNames_Temp
WHILE @.iRowCount > 0
BEGIN
SELECT @.sTableName = table_name from @.t_TableNames_Temp
SELECT @.sSQL = 'DROP DATABASE '+@.sTableName
EXEC (@.sSQL)
DELETE FROM @.t_TableNames_Temp WHERE @.sTableName = table_name
SELECT @.iRowCount = @.iRowCount - 1
END
RETURN 0
SET NOCOUNT OFF
GO
"Mark Broadbent" wrote:

> you could also use the ms undocumented command sp_foreachDB
> but would still need to check for system databases first.
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:%233QpAlPVHHA.192@.TK2MSFTNGP04.phx.gbl...
>
>

deleting multiple databases on 2000

Hi,
I need to delete approx 50 db on the same server. Instead of deleting one
at a time, is there a better way? Is there a script that someone can suggest
many thxAssuming you have only a couple of database that you want to keep, you could
create a script that uses a cursor to to select the databases
(master..sysdatabases) that you want to keep and then dynamically drop
eveything that is 'not in' your selected databases to keep.
Be warned!!! - make sure you eliminate the system databases.
If it were me, i'd just drop them manually just to be safe!
Immy
p.s. Ensure you have them all backed up! ;)
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:B860DA2F-705A-446C-AEC0-11B16350F3DB@.microsoft.com...
> Hi,
> I need to delete approx 50 db on the same server. Instead of deleting one
> at a time, is there a better way? Is there a script that someone can
> suggest
> many thx|||you could also use the ms undocumented command sp_foreachDB
but would still need to check for system databases first.
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:%233QpAlPVHHA.192@.TK2MSFTNGP04.phx.gbl...
> Assuming you have only a couple of database that you want to keep, you
> could create a script that uses a cursor to to select the databases
> (master..sysdatabases) that you want to keep and then dynamically drop
> eveything that is 'not in' your selected databases to keep.
> Be warned!!! - make sure you eliminate the system databases.
> If it were me, i'd just drop them manually just to be safe!
> Immy
> p.s. Ensure you have them all backed up! ;)
>
> "stoney" <stoney@.discussions.microsoft.com> wrote in message
> news:B860DA2F-705A-446C-AEC0-11B16350F3DB@.microsoft.com...
>> Hi,
>> I need to delete approx 50 db on the same server. Instead of deleting
>> one
>> at a time, is there a better way? Is there a script that someone can
>> suggest
>> many thx
>|||Hi,
Please find the script for the same:
------
--Name : s_RebuildIndices
--Author : Pallavi
--Description : Rebuilds all table indices
--Notes :
--Date : 28 March 2006
Create PROCEDURE dbo.s_DropUserdatabases
AS
SET NOCOUNT ON
-- declare all variables
DECLARE @.sTableName SYSNAME
DECLARE @.sSQL VARCHAR(50)
DECLARE @.iRowCount INT
DECLARE @.t_TableNames_Temp TABLE
(table_name SYSNAME)
INSERT @.t_TableNames_Temp
SELECT name
FROM SYSDATABASES
WHERE name not in ('master','msdb','model','tempdb')
ORDER BY name
--Getting row count from table
SELECT @.iRowCount = COUNT(*) FROM @.t_TableNames_Temp
WHILE @.iRowCount > 0
BEGIN
SELECT @.sTableName = table_name from @.t_TableNames_Temp
SELECT @.sSQL = 'DROP DATABASE '+@.sTableName
EXEC (@.sSQL)
DELETE FROM @.t_TableNames_Temp WHERE @.sTableName = table_name
SELECT @.iRowCount = @.iRowCount - 1
END
RETURN 0
SET NOCOUNT OFF
GO
"Mark Broadbent" wrote:
> you could also use the ms undocumented command sp_foreachDB
> but would still need to check for system databases first.
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:%233QpAlPVHHA.192@.TK2MSFTNGP04.phx.gbl...
> > Assuming you have only a couple of database that you want to keep, you
> > could create a script that uses a cursor to to select the databases
> > (master..sysdatabases) that you want to keep and then dynamically drop
> > eveything that is 'not in' your selected databases to keep.
> >
> > Be warned!!! - make sure you eliminate the system databases.
> >
> > If it were me, i'd just drop them manually just to be safe!
> > Immy
> > p.s. Ensure you have them all backed up! ;)
> >
> >
> > "stoney" <stoney@.discussions.microsoft.com> wrote in message
> > news:B860DA2F-705A-446C-AEC0-11B16350F3DB@.microsoft.com...
> >> Hi,
> >>
> >> I need to delete approx 50 db on the same server. Instead of deleting
> >> one
> >> at a time, is there a better way? Is there a script that someone can
> >> suggest
> >>
> >> many thx
> >
> >
>
>

Deleting a record in multiple table

I want to delete a client record, and that record is in several tables. e.g.
A client who has a record in persoanl information table, and a record in the
address table.
Is there a quick way to delete them all?
Thanks,You can use the ON DELETE CASCADE option on your foreign keys. You
could implement the equivalent in a trigger also. Otherwise you'll have
to do a separate DELETE for each table.
David Portas
SQL Server MVP
--

Deletes From Multiple Tables.

Hello everybody,

We have a Master table in the Db who's PK is referenced in at least 60 tables as FK.
We want to delete all the dependent records in 1 go without using multiple delete statements or any cursor or loop.

Is it possible? Please advice.

Thanks in advance.

Regards,

Ashishuse "Cascade Delete"...|||I'm sorry i forgot to mention that the Cascading Deletes are not allowed.

Even wrtting a trigger to delete from all the tables will mean writing some kind of loop or Cursor if we use the system tables.
I can use sys tables but the delete qury using sys tbales needs to execute in 1 go. No cursors or temp tables should be used.

Is there any other way?|||I'm sorry i forgot to mention that the Cascading Deletes are not allowed.
Frickin' morons.
My advice is to go work for a company that isn't managed by idiots.|||Now there's the blind dude we all know and love|||No cascading deletes allowed.
No temp tables allowed.
No cursors allowed. (Well, at least they are on the right track with that one...)

Absolute amateurs.

What advice would you give them?|||What advice would you give them?

Well, it realy sounds like a request for homework...but

DECLARE @.FKey sysname, @.keyvalue varchar(8000), @.sql varchar(8000)
SELECT @.FKey = 'EmployeeID', @.keyValue = '1', @.sql = ''

SELECT @.sql = @.sql + 'DELETE FROM ['+o.name+'] WHERE '+@.FKey+'='+@.KeyValue+' GO '
FROM sysreferences r
LEFT JOIN sysobjects o
ON r.fkeyid = o.id
WHERE rkeyid = OBJECT_ID('Employees')

SELECT @.sql

--EXEC(@.sql)|||Thanks dear friends.

Brett, it's not a homework at all.

In fact, i'm working on the one of the largest .Net implementations in the world. But somehow we have some basic guidelines which do not allow certain things.

Me being the modest developer cannot argue with the people who have set these guidelines. Please forgive those poor souls for their ignorance.

Thanks once again. :)|||In fact, i'm working on the one of the largest .Net implementations in the world. But somehow we have some basic guidelines which do not allow certain things
Using cascading deletes is the best solution for this problem (and is supported by all major DBMS).
If the "world largest" project does not apply the most effectice solutions to such a basic problem, then I'm pretty sure it is going to fail. This is extremely short-sighted and will create more maintenance trouble then they probably are willing to pay for. Why not use flat files, if they don't want to use the features of a database?
(nothing personal against you wash, it's just that I see this kind of stupid decisions all around...)|||Man, i don't mind it as long as they are paying me for the job. [:D]

I do not exactly know what has prompted the TAs to avoid cascade deletes.|||In fact, i'm working on the one of the largest .Net implementations in the world. But somehow we have some basic guidelines which do not allow certain things.

reading this made think he was working for the US federal government. in which case it may very well fail. The FBI can not get their case management software together, the IRS has been struggling for years to modernize.|||reading this made think he was working for the US federal government. in which case it may very well fail. The FBI can not get their case management software together, the IRS has been struggling for years to modernize.

Forgive me but i don't work on any such project.|||I have had everyone in my team at my company, including the so called SQL experts working with me, brainstorm on this issue.

It seems without a recursive loop , we cannot achieve this.

Can you give me any idea if we can achieve this using a simple loop.

I want to delete all the dependent records to the Nth level.

Thanks in advance.|||HAve one of your "sql experts" get in on this thread...and did you even look at my code?|||What in the world would drive such requirements? It makes as much sense as "We'd like to retrieve all the data from the database but I can't reference any table names or use the word 'select'".

I'm also guessing that you're going to generate a heap of log activity with this puppy, so you might want to think about doing a set of tables at a time or a set of primary key values, unless you own unlimited disk.|||Thanks Brett. i did use your code but it useful only for the first level of dependencies.

Thankfully people have realized what kind of issues have cropped up due to such requirement and thankfully we are working on some realistic solution.

I would like to thank everyone for chipping in with helpful suggestions and valuable advice.

thanks once again.

delete trigger for multiple row deletes

I'm racking my brain trying to figure this one out and I have a feeling it's
simpler than I'm making it out to be. I need a trigger that will prevent
delete of certain rows but not all. For example, I have a customer table:
CustID INT (primary key, auto inc)
CustName VARCHAR(50)
and an orders table:
OrderID INT (primary key, auto inc)
CustID INT (FK to other table of course)
ItemOrdered VARCHAR(100)
Here would be the data:
Customer table:
CustID CustName
1 Jones Lumber
2 Smith's Cars
3 Helen's Carpet
Orders table:
OrderID CustID ItemOrdered
1 1 2x4's
2 1 Dowel
3 3 Berber
4 3 Shag
Notice that Smith's Cars is not in the orders table at all.
Now I select all the rows in the customer table and try to delete them. I'd
like the trigger to prevent the delete of any customer that has orders but
allow the others to be deleted. So in this case Jones Lumber and Helen's
Carpet would remain and Smith's Cars would be deleted.
The trouble I'm having is that if I set up a cursor in the trigger to
sequence through the customers, how do I rollback just some of the deletes
but not all and put up an appropriate message?
DECLARE
@.iCustID AS INT,
@.sMsg AS VARCHAR(500)
SET NOCOUNT ON
SET @.sMsg = ''
DECLARE curCustomers CURSOR FOR SELECT C.CustID FROM DELETED C
OPEN curCustomers
FETCH NEXT FROM curCustomers INTO @.iCustID
IF @.@.FETCH_STATUS = 0
BEGIN
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (SELECT COUNT(*) FROM tblOrders WHERE CustID = @.iCustID) > 0
BEGIN
ROLLBACK TRANSACTION
SET @.sMsg = 'Customers with orders cannot be deleted.'
END
FETCH NEXT FROM curCustomers INTO @.iCustID
END
END
IF @.sMsg <> ''
RAISERROR (@.sMsg, 16, 1)
CLOSE curCustomers
DEALLOCATE curCustomers
===
My original trigger looked like this until I realized it was rolling back
the whole thing without even attempting to test for rows that should be
allowed to be deleted:
IF (SELECT COUNT(*) FROM tblOrders WHERE CustID IN (SELECT CustID FROM
DELETED)) > 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('Customers with orders cannot be deleted.', 16, 1)
END
Thanks,
KeithKeith G Hicks wrote:
> I'm racking my brain trying to figure this one out and I have a feeling it
's
> simpler than I'm making it out to be. I need a trigger that will prevent
> delete of certain rows but not all. For example, I have a customer table:
> CustID INT (primary key, auto inc)
> CustName VARCHAR(50)
> and an orders table:
> OrderID INT (primary key, auto inc)
> CustID INT (FK to other table of course)
> ItemOrdered VARCHAR(100)
> Here would be the data:
> Customer table:
> CustID CustName
> 1 Jones Lumber
> 2 Smith's Cars
> 3 Helen's Carpet
> Orders table:
> OrderID CustID ItemOrdered
> 1 1 2x4's
> 2 1 Dowel
> 3 3 Berber
> 4 3 Shag
> Notice that Smith's Cars is not in the orders table at all.
> Now I select all the rows in the customer table and try to delete them. I'
d
> like the trigger to prevent the delete of any customer that has orders but
> allow the others to be deleted. So in this case Jones Lumber and Helen's
> Carpet would remain and Smith's Cars would be deleted.
> The trouble I'm having is that if I set up a cursor in the trigger to
> sequence through the customers, how do I rollback just some of the deletes
> but not all and put up an appropriate message?
> DECLARE
> @.iCustID AS INT,
> @.sMsg AS VARCHAR(500)
> SET NOCOUNT ON
> SET @.sMsg = ''
> DECLARE curCustomers CURSOR FOR SELECT C.CustID FROM DELETED C
> OPEN curCustomers
> FETCH NEXT FROM curCustomers INTO @.iCustID
> IF @.@.FETCH_STATUS = 0
> BEGIN
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF (SELECT COUNT(*) FROM tblOrders WHERE CustID = @.iCustID) > 0
> BEGIN
> ROLLBACK TRANSACTION
> SET @.sMsg = 'Customers with orders cannot be deleted.'
> END
> FETCH NEXT FROM curCustomers INTO @.iCustID
> END
> END
> IF @.sMsg <> ''
> RAISERROR (@.sMsg, 16, 1)
> CLOSE curCustomers
> DEALLOCATE curCustomers
>
> ===
> My original trigger looked like this until I realized it was rolling back
> the whole thing without even attempting to test for rows that should be
> allowed to be deleted:
> IF (SELECT COUNT(*) FROM tblOrders WHERE CustID IN (SELECT CustID FROM
> DELETED)) > 0
> BEGIN
> ROLLBACK TRANSACTION
> RAISERROR ('Customers with orders cannot be deleted.', 16, 1)
> END
> Thanks,
> Keith
Do not use cursors in triggers (or anywhere else you don't have to for
that matter).
I've included a solution below but I don't really see the point. Why
not just modify your original DELETE statement to exclude the
referenced rows using the NOT EXISTS check? The foreign key keeps you
safe from accidental deletes.
CREATE TRIGGER trg_customer
ON customer INSTEAD OF DELETE
AS
IF EXISTS
(SELECT *
FROM deleted
WHERE EXISTS
(SELECT *
FROM orders
WHERE custid = deleted.custid))
RAISERROR ('Customers with orders cannot be deleted.', 16, 1);
DELETE FROM customer
WHERE EXISTS
(SELECT *
FROM deleted
WHERE custid = customer.custid)
AND NOT EXISTS
(SELECT *
FROM orders
WHERE custid = customer.custid);
GO
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
--|||Ok so just so I understand your strategy, a rollback is not necessary since
you doing INSETAD OF and are putting up a warning if there are any rows that
cannot be deleted and then actually performing the delete on the allowed
rows within the trigger?
The foreign key won't help me here. The actual project is slightly more
complex but my sample got the point across and your solution should do the
trick nicely. Very much appreciated.
What's the reason for avoiding cursors? Is it a performance issue or are
there other reasons?
Thanks again,
Keith
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1140822038.662268.199890@.e56g2000cwe.googlegroups.com...
Do not use cursors in triggers (or anywhere else you don't have to for
that matter).
I've included a solution below but I don't really see the point. Why
not just modify your original DELETE statement to exclude the
referenced rows using the NOT EXISTS check? The foreign key keeps you
safe from accidental deletes.
CREATE TRIGGER trg_customer
ON customer INSTEAD OF DELETE
AS
IF EXISTS
(SELECT *
FROM deleted
WHERE EXISTS
(SELECT *
FROM orders
WHERE custid = deleted.custid))
RAISERROR ('Customers with orders cannot be deleted.', 16, 1);
DELETE FROM customer
WHERE EXISTS
(SELECT *
FROM deleted
WHERE custid = customer.custid)
AND NOT EXISTS
(SELECT *
FROM orders
WHERE custid = customer.custid);
GO
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
--|||I implemented your suggestion and it works fine if I swap the sections so
that the test for Raiserror is happening after the delete is actually done.
Otherwise nothing deletes for me if it's done in the order you have below.
Can you explain why?
Thanks,
Keith
CREATE TRIGGER trg_customer
ON customer INSTEAD OF DELETE
AS
DELETE FROM customer
WHERE EXISTS
(SELECT *
FROM deleted
WHERE custid = customer.custid)
AND NOT EXISTS
(SELECT *
FROM orders
WHERE custid = customer.custid);
IF EXISTS
(SELECT *
FROM deleted
WHERE EXISTS
(SELECT *
FROM orders
WHERE custid = deleted.custid))
RAISERROR ('Customers with orders cannot be deleted.', 16, 1);
GO
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
--|||Never mind on this question. It works fine either way.
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:e6xNt2ZOGHA.2604@.TK2MSFTNGP09.phx.gbl...
I implemented your suggestion and it works fine if I swap the sections so
that the test for Raiserror is happening after the delete is actually done.
Otherwise nothing deletes for me if it's done in the order you have below.
Can you explain why?
Thanks,
Keith|||There are several reasons to avoid cursors. Set-based operations generally
perform better: heap and index maintenance can be optimized, and transaction
logging is minimized. In addition, triggers fire only once for a set-based
operation, whereas they fire once per iteration for a row-based operation.
But these aren't the only reasons. Cursors reduce the effective isolation
level to REPEATABLE READ if a transaction is outstanding or to READ
COMMITTED if one isn't. Also, if a transaction is outstanding and
modifications occur within the fetch loop, the probability of deadlocks
increases significantly. You should know that a transaction is always
outstanding within the body of a trigger. While there are rare occasions
where a cursor will improve performance, all of the above factors must be
taken into account before converting a set-based solution into a row-based
one. An important note: you should always attempt a set-based solution
first, and only after every other option has failed, such as adding indexes,
hints, and ultimately SET FORCEPLAN, should you convert a set-based solution
to a row-based one. As you can see, cursors are not for the uninitiated.
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:%23uVtziZOGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Ok so just so I understand your strategy, a rollback is not necessary
> since
> you doing INSETAD OF and are putting up a warning if there are any rows
> that
> cannot be deleted and then actually performing the delete on the allowed
> rows within the trigger?
> The foreign key won't help me here. The actual project is slightly more
> complex but my sample got the point across and your solution should do the
> trick nicely. Very much appreciated.
> What's the reason for avoiding cursors? Is it a performance issue or are
> there other reasons?
> Thanks again,
> Keith
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1140822038.662268.199890@.e56g2000cwe.googlegroups.com...
>
> Do not use cursors in triggers (or anywhere else you don't have to for
> that matter).
> I've included a solution below but I don't really see the point. Why
> not just modify your original DELETE statement to exclude the
> referenced rows using the NOT EXISTS check? The foreign key keeps you
> safe from accidental deletes.
> CREATE TRIGGER trg_customer
> ON customer INSTEAD OF DELETE
> AS
> IF EXISTS
> (SELECT *
> FROM deleted
> WHERE EXISTS
> (SELECT *
> FROM orders
> WHERE custid = deleted.custid))
> RAISERROR ('Customers with orders cannot be deleted.', 16, 1);
> DELETE FROM customer
> WHERE EXISTS
> (SELECT *
> FROM deleted
> WHERE custid = customer.custid)
> AND NOT EXISTS
> (SELECT *
> FROM orders
> WHERE custid = customer.custid);
> GO
> --
> 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
> --
>|||Thanks for all the info Brian.

Delete multiple tables?

SQL Server 2005:
How can I select and delete multiple tables in Management Studio?
Olav"Olav" <x@.y.com> wrote in message
news:%23nm2%23laXGHA.1204@.TK2MSFTNGP04.phx.gbl...
> SQL Server 2005:
> How can I select and delete multiple tables in Management Studio?
> Olav
>
Do you mean DROP the tables or do you mean DELETE the data from tables?
/* Delete ALL data from 3 tables */
DELETE FROM table1 ;
DELETE FROM table2 ;
DELETE FROM table3 ;
/* Drop 3 tables */
DROP TABLE table1 ;
DROP TABLE table2 ;
DROP TABLE table3 ;
Make sure you have a backup before you try these!
--
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
--|||I did mean from the GUI...
Seems like all the DBAs only work in a command line.
Olav
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:envtF9aXGHA.752@.TK2MSFTNGP02.phx.gbl...
> "Olav" <x@.y.com> wrote in message
> news:%23nm2%23laXGHA.1204@.TK2MSFTNGP04.phx.gbl...
>> SQL Server 2005:
>> How can I select and delete multiple tables in Management Studio?
>> Olav
> Do you mean DROP the tables or do you mean DELETE the data from tables?
> /* Delete ALL data from 3 tables */
> DELETE FROM table1 ;
> DELETE FROM table2 ;
> DELETE FROM table3 ;
> /* Drop 3 tables */
> DROP TABLE table1 ;
> DROP TABLE table2 ;
> DROP TABLE table3 ;
> Make sure you have a backup before you try these!
> --
> 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
> --
>|||"Olav" <x@.y.com> wrote in message
news:uo72tPbXGHA.1204@.TK2MSFTNGP04.phx.gbl...
>I did mean from the GUI...
> Seems like all the DBAs only work in a command line.
>
All the DBAs I know do. I would only recommend the GUI for development - not
on production servers.
If you want to drop multiple tables you can in fact do it from the GUI.
Select the tables node in the Object Explorer, then press F7 for the summary
view. You can now use shift+click to multi-select the tables, then hit
DELETE and click the button to drop them.
I don't think you can delete from more than one table without writing a
script.
--
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
--|||On Wed, 12 Apr 2006 00:04:36 +0200, Olav wrote:
>I did mean from the GUI...
Hi Olav,
Use click, shift-click, and ctrl-click to select the tables to delete;
Right-click one of the selected tables;
Click "delete".
>Seems like all the DBAs only work in a command line.
Indeed. The GUI has some bugs, some strange behaviour, and lacks some of
the finer control possibilities that the command line gives.
Once you've leared to use the SQL Statements, you'll find yourself using
the GUI less and less.
--
Hugo Kornelis, SQL Server MVP

Delete multiple tables?

SQL Server 2005:
How can I select and delete multiple tables in Management Studio?
Olav"Olav" <x@.y.com> wrote in message
news:%23nm2%23laXGHA.1204@.TK2MSFTNGP04.phx.gbl...
> SQL Server 2005:
> How can I select and delete multiple tables in Management Studio?
> Olav
>
Do you mean DROP the tables or do you mean DELETE the data from tables?
/* Delete ALL data from 3 tables */
DELETE FROM table1 ;
DELETE FROM table2 ;
DELETE FROM table3 ;
/* Drop 3 tables */
DROP TABLE table1 ;
DROP TABLE table2 ;
DROP TABLE table3 ;
Make sure you have a backup before you try these!
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
--|||I did mean from the GUI...
Seems like all the DBAs only work in a command line.
Olav
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:envtF9aXGHA.752@.TK2MSFTNGP02.phx.gbl...
> "Olav" <x@.y.com> wrote in message
> news:%23nm2%23laXGHA.1204@.TK2MSFTNGP04.phx.gbl...
> Do you mean DROP the tables or do you mean DELETE the data from tables?
> /* Delete ALL data from 3 tables */
> DELETE FROM table1 ;
> DELETE FROM table2 ;
> DELETE FROM table3 ;
> /* Drop 3 tables */
> DROP TABLE table1 ;
> DROP TABLE table2 ;
> DROP TABLE table3 ;
> Make sure you have a backup before you try these!
> --
> 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
> --
>|||"Olav" <x@.y.com> wrote in message
news:uo72tPbXGHA.1204@.TK2MSFTNGP04.phx.gbl...
>I did mean from the GUI...
> Seems like all the DBAs only work in a command line.
>
All the DBAs I know do. I would only recommend the GUI for development - not
on production servers.
If you want to drop multiple tables you can in fact do it from the GUI.
Select the tables node in the Object Explorer, then press F7 for the summary
view. You can now use shift+click to multi-select the tables, then hit
DELETE and click the button to drop them.
I don't think you can delete from more than one table without writing a
script.
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
--|||On Wed, 12 Apr 2006 00:04:36 +0200, Olav wrote:

>I did mean from the GUI...
Hi Olav,
Use click, shift-click, and ctrl-click to select the tables to delete;
Right-click one of the selected tables;
Click "delete".

>Seems like all the DBAs only work in a command line.
Indeed. The GUI has some bugs, some strange behaviour, and lacks some of
the finer control possibilities that the command line gives.
Once you've leared to use the SQL Statements, you'll find yourself using
the GUI less and less.
Hugo Kornelis, SQL Server MVP

Delete multiple tables

Hi everyone,

I spent a lot of time on this, but still have no idea how to get it working

This are the tables with forign keys that I have:

Table1 has table_1_id, table_1_data columns
Table2 has table_1_id, table_2_id, table_2_data columns
Table3 has table_2_id, table_3_id, table_3_data columns
Table4 has table_3_id, table_4_id, table_4_data columns

If I delete a row in table1, any related rows in table2 should be deleted, and at the same time, any rows in table3 which are related to table2 and related to table1 should be deleted as well and so on...

is there a way that I can write a SQL query to do it?

I am using MS SQL 2000 and ASP.net C#

Many thanks

EricLookup "cascading referential integrity constraints" in BOL, you can use the on delete cascade function for your foreign keys

HTH|||Originally posted by rhigdon
Lookup "cascading referential integrity constraints" in BOL, you can use the on delete cascade function for your foreign keys

HTH

Thanks for the information, this gives me a good hint. But I don't know what "BOL" means, could you explain it to me?

Thanks a lot

Eric|||Sorry, BOL is Books-online. It comes installed with SQL and is the best resource a SQL developer has (in the grand scheme of things) If you do not have it installed, you should and if you have not downloaded the latest BOL from MS (SP3A) you should and can at this location.

http://www.microsoft.com/downloads/details.aspx?FamilyID=683D71A6-BCF4-45A6-A2E2-F6AB5BE3EF12&displaylang=en

GL

delete multiple tables

i have a inherited adatabase application that creates a table everyday.
because a table is created evryday the database is becoming huge. after muc
h
deliberation i have decided that i only need to keep 30 days of tables. how
can i delete all tables older than 30 days inside the database? i am fairly
new to sql and i have not been able to figure out how to delete multiple
tables based on creation date.
ronnieHi Ronnie
Ur idea in deleting old tables looks good but never try to delete a table
based on the date they were created.
This might also delete your production tables in the database which are
important
create your tables for ex: ForDelete_<Table_name>_<date>
and now u can write a stored procedure to loop arround and delete these kind
of tables.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Ronnie" wrote:

> i have a inherited adatabase application that creates a table everyday.
> because a table is created evryday the database is becoming huge. after m
uch
> deliberation i have decided that i only need to keep 30 days of tables. h
ow
> can i delete all tables older than 30 days inside the database? i am fair
ly
> new to sql and i have not been able to figure out how to delete multiple
> tables based on creation date.
> ronnie|||See if this helps, but first be sure to have a backup of the db before using
it in production.
use your_db
go
declare @.sql nvarchar(4000)
declare @.ts sysname
declare @.tn sysname
declare @.cd datetime
declare tables_older_than_30_days cursor local fast_forward
for
select
user_name(uid) as table_schema,
[name] as table_name,
crdate
from
sysobjects
where
xtype = 'U'
and objectproperty([id], 'IsMSShipped') = 0
and datediff(day, crdate, getdate()) > 30
open tables_older_than_30_days
while 1 = 1
begin
fetch next from tables_older_than_30_days into @.ts, @.tn, @.cd
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'drop table ' + quotename(@.ts) + N'.' + quotename(@.tn)
print @.sql
-- uncomment this line to drop the table
-- exec sp_executesql @.sql
end
close tables_older_than_30_days
deallocate tables_older_than_30_days
go
AMB
"Ronnie" wrote:

> i have a inherited adatabase application that creates a table everyday.
> because a table is created evryday the database is becoming huge. after m
uch
> deliberation i have decided that i only need to keep 30 days of tables. h
ow
> can i delete all tables older than 30 days inside the database? i am fair
ly
> new to sql and i have not been able to figure out how to delete multiple
> tables based on creation date.
> ronnie|||Ronny
Wow , it looks to me you have a poor designed database
Why do you/someone else need to create a table every day?
In sysobjects system table there is a cdate column .
"Ronnie" <Ronnie @.discussions.microsoft.com> wrote in message
news:BC19E550-1D16-4B6D-952A-31CA103B2FE3@.microsoft.com...
> i have a inherited adatabase application that creates a table everyday.
> because a table is created evryday the database is becoming huge. after
much
> deliberation i have decided that i only need to keep 30 days of tables.
how
> can i delete all tables older than 30 days inside the database? i am
fairly
> new to sql and i have not been able to figure out how to delete multiple
> tables based on creation date.
> ronnie|||the database is for an automated vehicle location tacking. each day becomes
its own table. the table is populated with gps points for multiple vehicles
.
i am not sure why they set it up this way. i have not given it a lot of
thought yet. first i want to clean it up then look at redsigning it. thank
s
for the tip.
ronnie
"Uri Dimant" wrote:

> Ronny
> Wow , it looks to me you have a poor designed database
> Why do you/someone else need to create a table every day?
> In sysobjects system table there is a cdate column .
>
>
> "Ronnie" <Ronnie @.discussions.microsoft.com> wrote in message
> news:BC19E550-1D16-4B6D-952A-31CA103B2FE3@.microsoft.com...
> much
> how
> fairly
>
>

delete multiple rows with 2 ids

Hello All,
I have a table:
idSurrogate int identity
id1 int
id2 int

id1 + id2 is a unique index

I need to delete multiple rows from the table given a list of id1 and a list of id2
In other words
@.id1List = '10,20,30'
@.id2List = '1,3,5'
I need to delete these 3 rows from the table
1) @.id1=10 and @.id2=1
2) @.id1=20 and @.id2=3
3) @.id1=30 and @.id2=5

I am a bit lazy today - can anyone help out with a delete sql stmt

Thanks!

You could use a TVF that splits the ids into individual values. You can then write a SELECT like:

delete your_table

where exists(select * from (

select t1.val, t2.val

from dbo.split_string(@.id1List) as t1

join dbo.split_string(@.id2List) as t2

on t2.idx = t1.idx /* index is column that gives position of value in the string */

) as i(id1, id2)

where i.id1 = your_table.id1 and i.id2 = your_table.id2

)

See the link below for some ideas and samples on how to implement the TVF.

http://www.sommarskog.se/arrays-in-sql.html

Alternatively, you can modify your SP interface such that you pass the Ids as separate parameters. You need to however fix the maximum number of id pairs and this method is restricted to the number of parameters for a SP. You can then do something like:

delete your_table

where (id1 = @.id1_1 and id2 = @.id2_1)

or (id1 = @.id1_2 and id2 = @.id2_2)

or (id1 = @.id1_3 and id2 = @.id2_3)

...

-- or

delete your_table

where exists(select * from (

select @.id1_1, @.id2_1

union all

select @.id1_2, @.id2_2

...

) as i(id1, id2)

where i.id1 = your_table.id1 and i.id2 = your_table.id2

)

|||Yes, that should do the trick.
I happen to have a 'List_To_Table' table value udf that includes an identity column.

Thanks for the help - I greatly appreciate it|||

Lazy...big time...I would never respond to this...but

Delete from MyTable

where ISEVEN(@.id1list) = TRUE

OR ISEVEN(@.idlist2) = FALSE

OR

Delete from MyTable

where @.id1 mod 10 = 0

OR @.id2 mod 10 <> 0

Write a UDF (User Defined Function)

Adamus

|||

Umachandar Jayachandran - MS wrote:

You could use a TVF that splits the ids into individual values. You can then write a SELECT like:

delete your_table

where exists(select * from (

select t1.val, t2.val

from dbo.split_string(@.id1List) as t1

join dbo.split_string(@.id2List) as t2

on t2.idx = t1.idx /* index is column that gives position of value in the string */

) as i(id1, id2)

where i.id1 = your_table.id1 and i.id2 = your_table.id2

)

See the link below for some ideas and samples on how to implement the TVF.

http://www.sommarskog.se/arrays-in-sql.html

Alternatively, you can modify your SP interface such that you pass the Ids as separate parameters. You need to however fix the maximum number of id pairs and this method is restricted to the number of parameters for a SP. You can then do something like:

delete your_table

where (id1 = @.id1_1 and id2 = @.id2_1)

or (id1 = @.id1_2 and id2 = @.id2_2)

or (id1 = @.id1_3 and id2 = @.id2_3)

...

-- or

delete your_table

where exists(select * from (

select @.id1_1, @.id2_1

union all

select @.id1_2, @.id2_2

...

) as i(id1, id2)

where i.id1 = your_table.id1 and i.id2 = your_table.id2

)

WTF is this garbage T-SQL101?

Adamus

|||

Adamus Turner wrote:

Lazy...big time...I would never respond to this...but

Delete from MyTable

where ISEVEN(@.id1list) = TRUE

OR ISEVEN(@.idlist2) = FALSE

OR

Delete from MyTable

where @.id1 mod 10 = 0

OR @.id2 mod 10 <> 0

Write a UDF (User Defined Function)

Adamus

Just out of quriosity, wouldnt this be just as effective:

delete from MyTable where id1 in @.idlist1 and id2 in @.idlist2|||HPEvju,
Re: Just out of quriosity, wouldnt this be just as effective:

delete from MyTable where id1 in @.idlist1 and id2 in @.idlist2

Not quite what i need
i want to delete @.id1=10 and @.id2=1
but i don't want to delete @.id1=10 and @.id2=3

Adamus ,
?
|||

Aha, didn't quite understand what you where trying to do at first. Anyways, why not pass the input as XML (assuming you are using sql 2005 that is) and do a join?

That way you can work with the data as if it was just another table.

|||

HPEvju wrote:

Aha, didn't quite understand what you where trying to do at first. Anyways, why not pass the input as XML (assuming you are using sql 2005 that is) and do a join?

That way you can work with the data as if it was just another table.

Xml would be lovely. Unfortunately, I am writing a stored procedure to accommodate a front end that passes two lists.
In other words - the front end says " hey delete these rows for me here are two ordered lists"|||

I used to do somthing like this in sql server 2000 with this user defined function:

CREATE function splitAry( @.aryTmp as varchar(8000), @.separator as char(1) = ',')
returns @.tmp table (
ident int IDENTITY (1, 1) NOT NULL ,
string varchar(35) )
AS
BEGIN
declare @.iPos int
set @.iPos = PATINDEX('%' + @.separator + '%',@.aryTmp)
WHILE LEN(@.aryTmp) > 0 and @.iPos > 0
BEGIN
set @.iPos = PATINDEX('%' + @.separator + '%',@.aryTmp)
IF @.iPos > 0
BEGIN
insert into @.tmp values(SUBSTRING(@.aryTmp,1,@.iPos-1))
set @.aryTmp = SUBSTRING(@.aryTmp,@.iPos+1,LEN(@.aryTmp)-@.iPos)
END
END
INSERT INTO @.tmp VALUES(@.aryTmp)
RETURN
END

that way you get two tables that you can easily join against.

|||HPEvju,

After reading Umachandar Jayachandran's answer, I was put on the right track and used my own udf that essentially does the same as your splitAry.

Thanks for all your help

Delete Multiple Records from Multiple Tables

What is the simplist/correct way to delete multiple records from multiple tables. Any help appreciated. Thanks! (Yes, I'm totally new to this.)

delete dbo.tblcase

where dbo.tblcase.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')

delete dbo.tblcaseclient

where dbo.tblcaseclient.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')

delete dbo.tblcaseinformation

where dbo.tblcaseinformation.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')

delete dbo.tblcaselawyer

where dbo.tblcaselawyer.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')

delete dbo.tblcaseprosecutor

where dbo.tblcaseprosecutor.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')The simplest method is to establish relationships between your case table and the child tables, and define those relationships to have cascading deletes.
Then just delete from your case table and let SQL Server handle the rest.

Delete Multiple IDs

Hi,

I am getting doubles for all my IncidentIDs in IncidentID column.

Ex:

Incident ID

13734

Incident ID

13734

Is there a delete statement to get rid of more then one Replication of the same ID?

Thanks

There are numerous ways available to achieve this

Simplest way is Copy the table Data to temporary table, delete the data in original table, select distinct data from temporary table and insert into the original table

|||isnt there a query i can use?|||

try like this

select * into #temptable from yourtable

Go

Delete from yourtable

Go

Insert yourtable

Select distinct * from #temptable

Go

Drop table #temptable

|||where did all my data go in the original table?|||

I didn't get you properly, did u executed the mock script ? what's the result?

|||

i excecuted the script you have me filling 'yourtable' as the name of my current table and leaving #temptable as is.

I cannot find any data now..

|||Can you provide me your table structure and sample data|||

Most of the table looks like this, There are close to 13,000 of these records. For some odd reason now theres 26,000 due to the double copy/replication of each record. i just want to delete the 2nd copy. After I ran your mock query everything is missing.

Columns: Sample Data:

Incident Id 1212

Description Cannot get program to run.

Resolution Double clicked wrong icon.

|||

I tested the procedure again, It is working as desired

Let me know the Exact sql statements you used