Deleting from three tables

I'm triyng to delete from three tables.

Issue Table:

Column Name Data Type Allow Nulls issueID int Unchecked name varchar(50) Unchecked title varchar(100) Checked description varchar(500) Checked crntIssue bit Checked frntPage int Checked archived bit Checked navOrder int Checked dateCreate datetime Unchecked

Outlook Table:

Column Name Data Type Allow Nulls ID int Unchecked menu bit Checked mnuOrder int Checked mnuLevel int Checked parent int Checked issueID int Unchecked masterPage varchar(100) Unchecked visible bit Checked name varchar(50) Checked title varchar(250) Unchecked description varchar(1000) Checked summary text Checked contents text Checked image varchar(50) Checked imgCaption varchar(1000) Checked approve bit Checked createDate datetime Unchecked

Links Table:

Column Name Data Type Allow Nulls lnkID int Unchecked linkFromID int Unchecked linkToID int Unchecked

Issue table contais all the magazine issues. Outlook table contains all the pages of the Issue table. And the Links table contains the links or connection between parent page and child page. So here's what I wanted to do. When I click the delete issue button, I want to delete any pages, links, and issue from three tables that matches the issue ID that I wanted to delete. So for example, if I wanted to delete issueID 2, all the pages in the Outlook table and any links of those pages(lnkFromID) that are in the Links table should be deleted too. The lnkFromID and lnkToID are foriegn key of Outlook.ID table. The lnkFromID is the parent and lnkToID is the child.

I was wondering that maybe I can three delete statements for three tables. Is this a possibility? So any help is much appreciated.

Again, help is still needed. It seems to me that these statements will delete from three tables.

DELETE * FROM [OLlinks],[Outlook] WHERE ([OLlinks].[linkToID] = [Outlook].[ID] AND [Outlook].[issueID] = @.ID)

DELETE * FROM [OLissue],[Outlook] WHERE ([OLissue].[issueID] = [Outlook].[issueID] AND [OLissue].[issueID] = @.ID)

However, is this a best practice and how do I execute two delete statemens in one call?

Delete problem

Have table with two cols:

pkg int,
eqp int

with ex. values:

pkg eqp
1 1
1 1
2 3
2 3
2 3
3 1
4 1
4 1
4 1

Need to delete some records, so at end eqp column says how many records with the same pkg - from example above need to delete one record with pkg 1 and two with pkg 4. Want to do this without cursor. Any help appreciated.Logic is not clear - could you add some details?|||Ok - back to example:

we have two records with pkg = 1 (for equal pkg, eqp will be also equal), both of them have eqp = 1 - which means I need only one of those records - other one should be deleted. There are also 3 records with pkg = 3, they have eqp = 3 - which means it's ok (3 records, eqp = 3). For pkg = 3 it's also ok., but for pkg = 4, eqp = 1 (so only one record with pkg = 4 should stay, other 2 should be deleted).

Does it make clear?|||use pubs
go
create table mytable99(pkg int,eqp int)
GO
insert into mytable99
select 1,1
UNION ALL
select 1,1
UNION ALL
select 2,3
UNION ALL
select 2,3
UNION ALL
select 2,3
UNION ALL
select 3,2
UNION ALL
select 3,2
UNION ALL
select 3,2
UNION ALL
select 3,2
UNION ALL
select 4,1
UNION ALL
select 4,1
UNION ALL
select 4,1
UNION ALL
select 4,1
GO
SELECT * FROM MYTABLE99
GO
DROP TABLE MYTABLE99
GO

Current resultset
pkg eqp
---- ----
1 1
1 1
2 3
2 3
2 3
3 2
3 2
3 2
3 2
4 1
4 1
4 1
4 1

Needed resultset
pkg eqp
---- ----
1 1
2 3
2 3
2 3
3 2
3 2
4 1

Let the deletes begin :)

I am working on it ... this is for help of other guys|||What about package 2?|||Originally posted by Brett Kaiser
What about package 2?

Both in my and Enigma examples no deletion is needed. For pkg = 2 eqp = 3, so max 3 records with pkg = 2 allowed (as it is).|||Originally posted by MST78
Both in my and Enigma examples no deletion is needed. For pkg = 2 eqp = 3, so max 3 records with pkg = 2 allowed (as it is).

Do you have id column for this table?|||Originally posted by snail
Do you have id column for this table?

Nope. Addition of ID column is possible - but for some reasons I'd like to avoid that. With ID it wouldn't be such problem for me.|||Sledge hammer anyone?

USE Northwind
GO

SET NOCOUNT ON
GO

CREATE TABLE myTable99(pkg int,eqp int)
GO

INSERT INTO mytable99(pkg, eqp)
SELECT 1,1 UNION ALL
SELECT 1,1 UNION ALL
SELECT 2,3 UNION ALL
SELECT 2,3 UNION ALL
SELECT 2,3 UNION ALL
SELECT 3,2 UNION ALL
SELECT 3,2 UNION ALL
SELECT 3,2 UNION ALL
SELECT 3,2 UNION ALL
SELECT 4,1 UNION ALL
SELECT 4,1 UNION ALL
SELECT 4,1 UNION ALL
SELECT 4,1
GO

SELECT * FROM myTable99
GO

DECLARE @.MIN_pkg int, @.MAX_pkg int, @.eqp int, @.sql varchar(8000)

CREATE TABLE #myTemp99(pkg int,eqp int)

SELECT @.MIN_pkg = MIN(pkg),@.MAX_pkg = MAX(pkg)
FROM myTable99

WHILE @.MIN_pkg <> @.MAX_pkg
BEGIN
SELECT TOP 1 @.eqp = eqp FROM myTable99 WHERE pkg = @.MAX_pkg

SELECT @.SQL = 'INSERT INTO #myTemp99(pkg, eqp)'
+ ' SELECT TOP ' + CONVERT(varchar(3),@.eqp)
+ 'pkg, eqp FROM myTable99 WHERE pkg = ' + CONVERT(varchar(3),@.MAX_pkg)

EXEC(@.SQL)

SELECT @.MAX_pkg = MAX([pkg])
FROM myTable99
WHERE [pkg] < @.MAX_pkg

END

SELECT TOP 1 @.eqp = eqp FROM myTable99 WHERE pkg = @.MIN_pkg

SELECT @.SQL = 'INSERT INTO #myTemp99(pkg, eqp)'
+ ' SELECT TOP ' + CONVERT(varchar(3),@.eqp)
+ 'pkg, eqp FROM myTable99 WHERE pkg = ' + CONVERT(varchar(3),@.MIN_pkg)

EXEC(@.SQL)

SELECT * FROM #myTemp99
GO

DROP TABLE #myTemp99
DROP TABLE myTable99
GO

SET NOCOUNT OFF
GO|||Thanks Brett :) I'll check it at once.|||Originally posted by MST78
Nope. Addition of ID column is possible - but for some reasons I'd like to avoid that. With ID it wouldn't be such problem for me.

You may try to redesign your table (or tables) because in future you'll have much more problems than this one. What Brett did just confirms this.|||Originally posted by snail
You may try to redesign your table (or tables) because in future you'll have much more problems than this one. What Brett did just confirms this.

I agree 100%.

And what I gave you is totally arbitrary.

It assumes that all your data is alike based on the package

I just thought it was a neat exercise...

god what a geek...|||I have no possibility to redesign this table (since it's not under my care) - I may only ask to add an ID column. I know it's ill-designed.

Thx for all :)

DELETE problem

I have a hierarchy of menu and sub menus setup using this table:
CREATE TABLE menu
(
id INT NOT NULL IDENTITY PRIMARY KEY,
name VARCHAR(30)NOT NULL,
parentID INTNULL --Null If Root Menu
)

I want all sub menus to be deleted when a parent menu is deleted. I
wasn't able to get a recursive procedure to work because I got an error
about multiple cursors w/ the same name.

The best I've come up w/ is this:

DELETE FROM menu WHERE id = x --Del Menu
--Cleanup Children
DELETE FROM menu WHERE parentID <> NULL AND parentID NOT IN(SELECT id
from menu)

Is there a better/faster way of doing this?Hi

If you did not see my reply to your previous post, the following is about
the best you can do

DELETE FROM menu WHERE id = x --Del Menu

WHILE @.@.ROWCOUNT > 0
BEGIN
DELETE FROM menu
WHERE parentID IS NOT NULL
AND parentID NOT IN (SELECT id FROM menu)
END

Do not use <> NULL

John

<wackyphill@.yahoo.com> wrote in message
news:1103306757.201621.73930@.z14g2000cwz.googlegro ups.com...
>I have a hierarchy of menu and sub menus setup using this table:
> CREATE TABLE menu
> (
> id INT NOT NULL IDENTITY PRIMARY KEY,
> name VARCHAR(30) NOT NULL,
> parentID INT NULL --Null If Root Menu
> )
> I want all sub menus to be deleted when a parent menu is deleted. I
> wasn't able to get a recursive procedure to work because I got an error
> about multiple cursors w/ the same name.
> The best I've come up w/ is this:
> DELETE FROM menu WHERE id = x --Del Menu
> --Cleanup Children
> DELETE FROM menu WHERE parentID <> NULL AND parentID NOT IN(SELECT id
> from menu)
> Is there a better/faster way of doing this?|||John Bell wrote:
> Hi
> If you did not see my reply to your previous post, the following is
about
> the best you can do
> DELETE FROM menu WHERE id = x --Del Menu
> WHILE @.@.ROWCOUNT > 0
> BEGIN
> DELETE FROM menu
> WHERE parentID IS NOT NULL
> AND parentID NOT IN (SELECT id FROM menu)
> END
> Do not use <> NULL
> John
> <wackyphill@.yahoo.com> wrote in message
> news:1103306757.201621.73930@.z14g2000cwz.googlegro ups.com...
> >I have a hierarchy of menu and sub menus setup using this table:
> > CREATE TABLE menu
> > (
> > id INT NOT NULL IDENTITY PRIMARY KEY,
> > name VARCHAR(30) NOT NULL,
> > parentID INT NULL --Null If Root Menu
> > )
> > I want all sub menus to be deleted when a parent menu is deleted. I
> > wasn't able to get a recursive procedure to work because I got an
error
> > about multiple cursors w/ the same name.
> > The best I've come up w/ is this:
> > DELETE FROM menu WHERE id = x --Del Menu
> > --Cleanup Children
> > DELETE FROM menu WHERE parentID <> NULL AND parentID NOT IN(SELECT
id
> > from menu)
> > Is there a better/faster way of doing this?|||Thanks, John. That works great!|||Sorry John I did miss your last post and you are right, that works very
well. Thank you very much.|||Look up the nested sets model for trees; this can be done without
procedural code.

DELETE on FK

I am having 2 tables :

products :

product_id (PK, int, not null)
reports_id (FK, int, not null)
name

reports :

reports_id (PK, int , not null)
dateR

If I : DELETE FROM reports WHERE reports_id = 100

how can I create a contraints in MS SQL 2000 that automaticly all the products with reports_id = 100 are deleted too ?

thank youChecked the "Cascade Delete Related Records" From Relationship|||but how can I do it as a script (FROM outside)
ALTER TABLE ... CONTRAINST ... ?

thank you|||Check this...

ALTER TABLE products WITH NOCHECK
ADD CONSTRAINT exd_check FOREIGN KEY
(
[reports_id]
) REFERENCES [reports] (
[reports_id]
) ON DELETE CASCADE|||thank you rudra !|||Rudra I am having a probleme the fields are not declared as FK in the database ( I have not created that database) can I use a ON DELETE CASCADE anyway on 2 similar fields ?

something like

ALTER TABLE products WITH NOCHECK
ADD CONSTRAINT exd_check ???
(
[reports_id]
) REFERENCES [reports] (
[reports_id]
) ON DELETE CASCADE

thank you|||why don't you create a on-delete trigger for this?|||i dont know how to do it and i run the commands from a .NET application|||Why are you trying to do this from a user interface? The job of writing triggers belongs to the developer, not the application.|||i want to put it in any way in the database no matter the way|||A trigger is a permanent database object. An application has no business creating such things on the fly.
Either create the trigger or don't, but don't ask us to help you shoot yourself in the foot.
If you insist upon handling relational integrity within the interface, then make two calls to the database. One to delete the child records, and one to delete the parent record.|||thank you for the trigger, and helping me for database code

for the rest i never worry for what i cannot change

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