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 where syntax ... need help :)

I have a table with the following columns,

NAME, TYPE, TAG

And there may be 'duplicates' on name and type.

How can I delete them??

I want to delete all with duplicate NAME and TYPEActually I want to delete all rows which is duplicate on NAME and
TYPE.

Name Type Tag
----------
TEST1 12 A
TEST1 12 B
TEST2 12 A
TEST4 14 B

If you take this example, I'd like to delete TEST1 and only have TEST2
and TEST4 left in my table.

This is a temporary table used to compare tables in different
databases.
I move all the tables from both databases into this temp table, and to
find the tables that are found only in on of the databases, I want to
perform the deletion as mentioned above.

The result should give me the tables (occurences) that is missing in
one of the databases. The TAG tells me which.|||Actually I want to delete all rows which is duplicate on NAME and

Quote:

Originally Posted by

TYPE.

You can remove the TAG criteria from the original statement I posted so that
all of the rows with duplicate NAME and TYPE values are removed:

CREATE TABLE dbo.PMTOOLS
(
[NAME] varchar(30) NOT NULL,
[TYPE] varchar(30) NOT NULL,
[TAG] varchar(30) NOT NULL
)
GO

INSERT INTO dbo.PMTOOLS
SELECT 'TEST1', '12', 'A'
UNION ALL SELECT 'TEST1', '12', 'B'
UNION ALL SELECT 'TEST2', '12', 'A'
UNION ALL SELECT 'TEST4', '14', 'B'
GO

DELETE dbo.PMTOOLS
FROM dbo.PMTOOLS
JOIN (
SELECT NAME, TYPE
FROM dbo.PMTOOLS
GROUP BY NAME, TYPE
HAVING COUNT(*) 1
) AS dups
ON
dups.NAME = PMTOOLS.NAME AND
dups.TYPE = PMTOOLS.TYPE

--
Hope this helps.

Dan Guzman
SQL Server MVP

"cobolman" <olafbrungot@.hotmail.comwrote in message
news:1183028881.884401.9280@.n60g2000hse.googlegrou ps.com...

Quote:

Originally Posted by

Actually I want to delete all rows which is duplicate on NAME and
TYPE.
>
Name Type Tag
----------
TEST1 12 A
TEST1 12 B
TEST2 12 A
TEST4 14 B
>
If you take this example, I'd like to delete TEST1 and only have TEST2
and TEST4 left in my table.
>
This is a temporary table used to compare tables in different
databases.
I move all the tables from both databases into this temp table, and to
find the tables that are found only in on of the databases, I want to
perform the deletion as mentioned above.
>
The result should give me the tables (occurences) that is missing in
one of the databases. The TAG tells me which.
>
>
>

|||On Jun 28, 4:50 am, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.netwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Actually I want to delete all rows which is duplicate on NAME and
TYPE.

>
You can remove the TAG criteria from the original statement I posted so that
all of the rows with duplicate NAME and TYPE values are removed:
>
CREATE TABLE dbo.PMTOOLS
(
[NAME] varchar(30) NOT NULL,
[TYPE] varchar(30) NOT NULL,
[TAG] varchar(30) NOT NULL
)
GO
>
INSERT INTO dbo.PMTOOLS
SELECT 'TEST1', '12', 'A'
UNION ALL SELECT 'TEST1', '12', 'B'
UNION ALL SELECT 'TEST2', '12', 'A'
UNION ALL SELECT 'TEST4', '14', 'B'
GO
>
DELETE dbo.PMTOOLS
FROM dbo.PMTOOLS
JOIN (
SELECT NAME, TYPE
FROM dbo.PMTOOLS
GROUP BY NAME, TYPE
HAVING COUNT(*) 1
) AS dups
ON
dups.NAME = PMTOOLS.NAME AND
dups.TYPE = PMTOOLS.TYPE
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"cobolman" <olafbrun...@.hotmail.comwrote in message
>
news:1183028881.884401.9280@.n60g2000hse.googlegrou ps.com...
>

Quote:

Originally Posted by

Actually I want to delete all rows which is duplicate on NAME and
TYPE.

>

Quote:

Originally Posted by

Name Type Tag
----------
TEST1 12 A
TEST1 12 B
TEST2 12 A
TEST4 14 B

>

Quote:

Originally Posted by

If you take this example, I'd like to delete TEST1 and only have TEST2
and TEST4 left in my table.

>

Quote:

Originally Posted by

This is a temporary table used to compare tables in different
databases.
I move all the tables from both databases into this temp table, and to
find the tables that are found only in on of the databases, I want to
perform the deletion as mentioned above.

>

Quote:

Originally Posted by

The result should give me the tables (occurence) that is missing in
one of the databases. The TAG tells me which.

cobolman,

I may be reading more into this than I should, but I am assuming you
want to keep one row for each set of dups. Dan's script will remove
all occurrences of the dup rows.

Do you have a sequential unique ID, or timestamp type of column on the
table? Let us know the details (schema) if you do and I'll post a
solution for you.

-- Bill|||Bill,

I do want to remove all occurences of the dup rows.
The result set should only hold the ones that did not have any dups.

Thanks to both (Dan and Bill) :)|||I guess I need help on another one as well, ...

I'd like to do a select to find all the foreign keys of a given table,
and the foreign_key columns.. (Sybase).

This SQL gives me what I want :

Select a.foreign_table_id, a.foreign_key_id, a.primary_table_id,
b.foreign_column_id, b.primary_column_id, c.column_id
from SYS.SYSFOREIGNKEY a
JOIN SYS.SYSFKCOL b ON
a.foreign_table_id = b.foreign_table_id AND
a.foreign_key_id = b.foreign_key_id
where a.foreign_table_id= XXX

But, .. what I'd really like is to instead of the column_id's and
table_id's have the actual name. I can get this from systable and
syscolumn, but I'm not sure how to write the sql|||Hmm...could this be it?

Select a.foreign_table_id, a.foreign_key_id, a.primary_table_id,
c.table_name, b.foreign_column_id,
(select column_name from sys.syscolumn where table_id =
a.foreign_table_id AND column_id = b.foreign_column_id),
b.primary_column_id,
(select column_name from sys.syscolumn where table_id =
a.primary_table_id AND column_id = b.primary_column_id)
from SYS.SYSFOREIGNKEY a
JOIN SYS.SYSFKCOL b ON
a.foreign_table_id = b.foreign_table_id AND
a.foreign_key_id = b.foreign_key_id
JOIN SYS.SYSTABLE c ON
a.primary_table_id = c.table_id
where a.foreign_table_id=XXX|||cobolman (olafbrungot@.hotmail.com) writes:

Quote:

Originally Posted by

I guess I need help on another one as well, ...
>
I'd like to do a select to find all the foreign keys of a given table,
and the foreign_key columns.. (Sybase).

You are probably better off asking in comp.databases.sybase. It does not
seem from your queries that neither Sybase use their old system
tables anymore.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

DELETE SQL Query Question - Joining a Table data type and a User Table.

Dear All,
Can anyone help me to run this below query ?
DELETE @._TCTableTemp FROM @._TCTableTemp , Scrip WHERE
@._TCTableTemp.Scrip_Code = Scrip.Scrip_Code
AND Scrip.Inst_Code LIKE 'N%' AND TC_CODE = 'STT'
The error that I am getting is:
Server: Msg 137, Level 15, State 2, Procedure STP_FORMATCCHARGE1, Line 360
Must declare the variable '@._TCTableTemp'.
Here @._TCTableTemp - Is a Table data type and Scrip is a User Table.
Thanks and Regards,
PeriPeri wrote:
> Dear All,
> Can anyone help me to run this below query ?
> DELETE @._TCTableTemp FROM @._TCTableTemp , Scrip WHERE
> @._TCTableTemp.Scrip_Code = Scrip.Scrip_Code
> AND Scrip.Inst_Code LIKE 'N%' AND TC_CODE = 'STT'
> The error that I am getting is:
> Server: Msg 137, Level 15, State 2, Procedure STP_FORMATCCHARGE1, Line 360
> Must declare the variable '@._TCTableTemp'.
> Here @._TCTableTemp - Is a Table data type and Scrip is a User Table.
> Thanks and Regards,
> Peri
Use an alias:
DELETE T
FROM @._TCTableTemp AS T, Scrip
WHERE T.scrip_code = Scrip.scrip_code
AND Scrip.inst_code LIKE 'N%'
AND tc_code = 'STT' ;
The table variable is only a temporary structure so in many cases I
wouldn't bother to delete from it. Just ignore the rows you planned to
delete.
David Portas
SQL Server MVP
--