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 row from MS SQL Server 2000

Hi, I'm using SQL Server 2000 Personal Edition. I have created a table 'Student' which has 2 fields.

ID -- Varchar(10), Primary Key - contains ID of a student

NAME -- Varchar(50) -- contains names of student

ID NAME

0107200701 abcd

0107200702 cdgdh

0107200703 iyiylklk

.

.

I want to delete a complete row (all entries) from the "student" table using/specifing 'ID'. What will be the SQL query for that?

Hi

Try to use this (and please read help )

delete from Student where [ID] = '0107200701'

You should change the ID to your real ID

Regards,

Janos

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 of mirrored rows

hi.
I've seen ways to delete duplicate rows.
Can someone give me some sql to do this?

I have a table with varchar table_name_start, varchar column_name,
varchar table_name_end;

it has rows like this:
table1 col1 table2
table1 col2 table 3
table2 col1 table1

I'd lke to delete the rows if they exist with the names swapped
around, i.e. like above since the first and third share a column name
and the table_name_start/end matches the others table_name_end/start,
I'd like to delete one and leave the other.

I'm scratching my head trying to figure this out.

thanksWhat is the key of this table? Please post proper DDL so that we don't
have to guess. I'm going to assume that the combination of all three
columns is unique, in which case try this:

DELETE FROM tbl
WHERE EXISTS
(SELECT *
FROM tbl AS T
WHERE T.table_name_start = tbl.table_name_end
AND T.table_name_end = tbl.table_name_start
AND T.column_name = T.column_name)
AND table_name_start > table_name_end ;

--
David Portas
SQL Server MVP
--|||On 6 Oct 2005 01:00:14 -0700, "David Portas"
<REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:

>What is the key of this table? Please post proper DDL so that we don't
>have to guess. I'm going to assume that the combination of all three
>columns is unique, in which case try this:
>DELETE FROM tbl
> WHERE EXISTS
> (SELECT *
> FROM tbl AS T
> WHERE T.table_name_start = tbl.table_name_end
> AND T.table_name_end = tbl.table_name_start
> AND T.column_name = T.column_name)
> AND table_name_start > table_name_end ;
>--
>David Portas
>SQL Server MVP
David,
Very sorry but I did not realize that would make a difference. (really
I didn't).
There actually was not a primary key (this was a temporary working
table).
I've put a primary key into place now, but I used the script before
that.
It seemed to work fine I've never used the 'AS' before.. I guess I
better study up a bit on it.
Oh.. the ddl was this:
CREATE TABLE [allEdges] (
[table_name_start] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[column_name] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[table_name_end] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO|||In this case the key makes a difference to the extent that my DELETE
statement will delete "mirrored" rows but won't delete all duplicates
(all three columns identical). For that you would need a key, a cursor
or an intermediate table. Also, my DELETE won't remove rows with NULLs,
which I can see may be an issue now that you've posted DDL with
nullable columns.

In any case, it makes sense to include keys with your DDL or to state
that your table doesn't have a key. Keys and constraints can make a big
difference to the solution.

--
David Portas
SQL Server MVP
--|||On 6 Oct 2005 03:53:10 -0700, "David Portas"
<REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:

>In this case the key makes a difference to the extent that my DELETE
>statement will delete "mirrored" rows but won't delete all duplicates
>(all three columns identical). For that you would need a key, a cursor
>or an intermediate table. Also, my DELETE won't remove rows with NULLs,
>which I can see may be an issue now that you've posted DDL with
>nullable columns.
>In any case, it makes sense to include keys with your DDL or to state
>that your table doesn't have a key. Keys and constraints can make a big
>difference to the solution.
>--
>David Portas
>SQL Server MVP
I understand completely. Thanks for the edifications.
(that's what we're here for, eh?)
Cheers|||another way of removing duplicates is to
select distinct * into new table
drop old table
rename the new table

Could be much faster|||On 6 Oct 2005 11:32:52 -0700, "Alexander Kuznetsov"
<AK_TIREDOFSPAM@.hotmail.COM> wrote:

>another way of removing duplicates is to
>select distinct * into new table
>drop old table
>rename the new table
>Could be much faster
I'm not sure because they are not exact duplicates.. just mirrors