March 23, 2009 at 8:55 am
given the table, I have a set of productcontrolID's that have many applicationID records.
Given the set of productcontrolid's, how do I eliminate (delete) all but one row. While also deleting the applicationid (record) in the application table first because it is required.
In a cursor I would just select the whole set, and skip the delete statement when the the productcontrolID changes.
USE [PartsTrakStaging]
GO
/****** Object: Table [dbo].[ApplicationProduct] Script Date: 03/23/2009 08:45:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ApplicationProduct](
[ApplicationProductID] [int] IDENTITY(1,1) NOT NULL,
[ApplicationID] [int] NOT NULL,
[ProductControlID] [int] NOT NULL,
[NumberRequired] [smallint] NULL,
[Sequence] [smallint] NOT NULL,
[SourceID] [int] NULL,
[ChangedDate] [datetime] NULL,
[ChangedType] [char](1) NULL,
[ChangedMachineName] [varchar](50) NULL,
CONSTRAINT [PK_ApplicationProduct2] PRIMARY KEY CLUSTERED
(
[ApplicationProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[ApplicationProduct] WITH NOCHECK ADD CONSTRAINT [FK_ApplicationProduct_Application2] FOREIGN KEY([ApplicationID])
REFERENCES [dbo].[Application] ([ApplicationID])
GO
ALTER TABLE [dbo].[ApplicationProduct] CHECK CONSTRAINT [FK_ApplicationProduct_Application2]
GO
ALTER TABLE [dbo].[ApplicationProduct] WITH NOCHECK ADD CONSTRAINT [FK_ApplicationProduct_ProductControl2] FOREIGN KEY([ProductControlID])
REFERENCES [dbo].[ProductControl] ([ProductControlID])
GO
ALTER TABLE [dbo].[ApplicationProduct] CHECK CONSTRAINT [FK_ApplicationProduct_ProductControl2]
GO
/****** Object: Table [dbo].[Application] Script Date: 03/23/2009 08:48:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Application](
[ApplicationID] [int] NOT NULL,
[ApplicationDescriptionID] [int] NOT NULL,
[ApplicationCommentID] [int] NOT NULL,
[NotRequired] [bit] NOT NULL,
[Sequence] [smallint] NOT NULL,
[sourceId] [int] NULL,
[ChangedDate] [datetime] NULL,
[ChangedType] [char](1) NULL,
CONSTRAINT [PK_Application] PRIMARY KEY CLUSTERED
(
[ApplicationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Application] WITH CHECK ADD CONSTRAINT [FK_Application_ApplicationComment] FOREIGN KEY([ApplicationCommentID])
REFERENCES [dbo].[ApplicationComment] ([ApplicationCommentID])
GO
ALTER TABLE [dbo].[Application] CHECK CONSTRAINT [FK_Application_ApplicationComment]
GO
ALTER TABLE [dbo].[Application] WITH CHECK ADD CONSTRAINT [FK_Application_ApplicationDescription] FOREIGN KEY([ApplicationDescriptionID])
REFERENCES [dbo].[ApplicationDescription] ([ApplicationDescriptionID])
GO
ALTER TABLE [dbo].[Application] CHECK CONSTRAINT [FK_Application_ApplicationDescription]
GO
March 23, 2009 at 9:03 am
I'm not clear on what your criteria are for which ones to delete, but one way to get rid of all but one row from a set is like this:
delete from dbo.ApplicationProduct
where ApplicationProductID = @Param
and ProductControlID not in
(select min(ProductControlID)
from dbo.ApplicationProduct
where ApplicationProductID = @Param);
Does that move you in the right direction?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 23, 2009 at 9:58 am
sorta right, but:
lets say this query gives me a set of the ones I want to delete.
It certainly doesn't perform as well as the first part.
SELECT DISTINCT Application.ApplicationID, NapaThirdParty.PN, Product.PartNumber, CategoryAAIAmap.GenCategoryID
FROM ApplicationProduct WITH (nolock) INNER JOIN
Application ON ApplicationProduct.ApplicationID = Application.ApplicationID INNER JOIN
NapaThirdParty INNER JOIN
Product ON NapaThirdParty.PN = Product.PartNumber INNER JOIN
CategoryAAIAmap ON NapaThirdParty.[AAIA ID] = CategoryAAIAmap.[AAIA ID] ON ApplicationProduct.ProductControlID = Product.ProductControlID
WHERE (Product.ProductLineID = 10290) and applicationproduct.applicationid not in (SELECT MIN(Application.ApplicationID) AS Expr1, ApplicationProduct.ProductControlID
FROM ApplicationProduct WITH (nolock) INNER JOIN
Application ON ApplicationProduct.ApplicationID = Application.ApplicationID INNER JOIN
NapaThirdParty INNER JOIN
Product ON NapaThirdParty.PN = Product.PartNumber INNER JOIN
CategoryAAIAmap ON NapaThirdParty.[AAIA ID] = CategoryAAIAmap.[AAIA ID] ON ApplicationProduct.ProductControlID = Product.ProductControlID
GROUP BY ApplicationProduct.ProductControlID)
March 23, 2009 at 10:41 am
If you have the cursor based code for doing this, it would help if you posted that as well. It will answer questions that will make rewriting in a set based manner easier.
March 23, 2009 at 10:46 am
I just finished code that should do that:
private void button1_Click(object sender, EventArgs e)
{
sql = " SELECT distinct Application.ApplicationID, ApplicationProduct.ProductControlID ";
sql += "FROM NapaThirdParty INNER JOIN ";
sql += "Application INNER JOIN ";
sql += "ApplicationProduct ON Application.ApplicationID = ApplicationProduct.ApplicationID INNER JOIN ";
sql += "Product ON ApplicationProduct.ProductControlID = Product.ProductControlID ON NapaThirdParty.PN = Product.PartNumber ";
sql += "WHERE (Product.ProductLineID = 10290)" ;
DataTable dt1 = new DataTable();
SqlConnection sconn = new SqlConnection(Properties.Settings.Default.ptstaging);
SqlDataAdapter sda = new SqlDataAdapter();
sda.SelectCommand = new SqlCommand();
sda.SelectCommand.Connection = sconn;
sda.UpdateCommand = new SqlCommand();
sda.UpdateCommand.Connection = sconn;
sda.SelectCommand.CommandText = sql;
sda.Fill(dt1);
string pcid = "";
string appid;
string sqldel1, sqldel2;
foreach (DataRow dr in dt1.Rows)
{
if (pcid == dr["ProductControlID"].ToString())
{
sqldel1 = "delete from application where applicationid = " + dr["ApplicationID"].ToString();
sda.UpdateCommand.CommandText = sqldel1;
sda.UpdateCommand.ExecuteNonQuery();
sqldel2 = "delete from applicationproduct where applicationid = " + dr["ApplicationID"].ToString();
sda.UpdateCommand.CommandText = sqldel2;
sda.UpdateCommand.ExecuteNonQuery();
}
pcid = dr["productcontrolid"].ToString();
}
}
March 23, 2009 at 10:55 am
There's a DELETE...FROM lurking in here:
SELECT DISTINCT a.ApplicationID, n.PN, p.PartNumber, c.GenCategoryID
FROM ApplicationProduct ap (nolock)
INNER JOIN Application a
ON ap.ApplicationID = a.ApplicationID
INNER JOIN Product p
ON ap.ProductControlID = p.ProductControlID
INNER JOIN NapaThirdParty n
ON n.PN = p.PartNumber
INNER JOIN CategoryAAIAmap c
ON n.[AAIA ID] = c.[AAIA ID]
WHERE p.ProductLineID = 10290
AND ap.applicationid NOT IN
(SELECT MIN(a.ApplicationID) AS Expr1, ap.ProductControlID
FROM ApplicationProduct ap WITH (nolock)
INNER JOIN Application a
ON ap.ApplicationID = a.ApplicationID
INNER JOIN Product p
ON ap.ProductControlID = p.ProductControlID
INNER JOIN NapaThirdParty n
ON n.PN = p.PartNumber
INNER JOIN CategoryAAIAmap c
ON n.[AAIA ID] = c.[AAIA ID]
GROUP BY ap.ProductControlID)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 23, 2009 at 11:21 am
Chris Morris (3/23/2009)
There's a DELETE...FROM lurking in here:
SELECT DISTINCT a.ApplicationID, n.PN, p.PartNumber, c.GenCategoryIDFROM ApplicationProduct ap (nolock)
INNER JOIN Application a
ON ap.ApplicationID = a.ApplicationID
INNER JOIN Product p
ON ap.ProductControlID = p.ProductControlID
INNER JOIN NapaThirdParty n
ON n.PN = p.PartNumber
INNER JOIN CategoryAAIAmap c
ON n.[AAIA ID] = c.[AAIA ID]
WHERE p.ProductLineID = 10290
AND ap.applicationid NOT IN
(SELECT MIN(a.ApplicationID) AS Expr1, ap.ProductControlID
FROM ApplicationProduct ap WITH (nolock)
INNER JOIN Application a
ON ap.ApplicationID = a.ApplicationID
INNER JOIN Product p
ON ap.ProductControlID = p.ProductControlID
INNER JOIN NapaThirdParty n
ON n.PN = p.PartNumber
INNER JOIN CategoryAAIAmap c
ON n.[AAIA ID] = c.[AAIA ID]
GROUP BY ap.ProductControlID)
problem with this is the last subquery seems to take forever on the server.
Take a look at the code posted above, and work it as setbased. Should be simple right?
I seem to recall something of "it's easier" nature to setbase solutions. However when it comes right down to it, I am having doubts.
March 23, 2009 at 11:28 am
foxjazz (3/23/2009)
problem with this is the last subquery seems to take forever on the server.Take a look at the code posted above, and work it as setbased. Should be simple right?
I seem to recall something of "it's easier" nature to setbase solutions. However when it comes right down to it, I am having doubts.
Do you need anything other than MIN(a.ApplicationID) in order to identify the rows you need to delete? Which table do you want to delete from?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 23, 2009 at 11:29 am
Try this in a test copy of the database, or wrapped in a transaction you can roll back.
DELETE
ApplicationProduct
FROM
dbo.ApplicationProduct
INNER JOIN dbo.Application
ON ApplicationProduct.ApplicationID = Application.ApplicationID
INNER JOIN dbo.Product
ON ApplicationProduct.ProductControlID = Product.ProductControlID
WHERE
(Product.ProductLineID = 10290)
and
applicationproduct.applicationid not in
(SELECT
MIN(Application.ApplicationID)
FROM
ApplicationProduct
GROUP BY
ApplicationProduct.ProductControlID);
See if that'll do what you need.
If it gets the end result you need, but it's too slow, post the execution plan.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 23, 2009 at 11:34 am
Since there is a constraint, I have to delete the applicationid from the application table first, then delete it from the applicationproduct table.
As seen in the code above.
March 23, 2009 at 11:34 am
First question, does the following query return the correct result set that you are deleting items based on?
SELECT distinct
Application.ApplicationID,
ApplicationProduct.ProductControlID
FROM
[Application]
INNER JOIN [ApplicationProduct]
ON Application.ApplicationID = ApplicationProduct.ApplicationID
INNER JOIN [Product]
ON ApplicationProduct.ProductControlID = Product.ProductControlID
INNER JOIN NapaThirdParty
ON NapaThirdParty.PN = Product.PartNumber
WHERE
(Product.ProductLineID = 10290)
March 23, 2009 at 11:37 am
Also, looking at your initial post, you only provided the DDL for two of the tables in the query. Really could use the DDL for the other two tables. Also, some test data and expected results would really allow us to test any code before posting it back on this thread.
March 23, 2009 at 11:37 am
foxjazz (3/23/2009)
Since there is a constraint, I have to delete the applicationid from the application table first, then delete it from the applicationproduct table.As seen in the code above.
Do you have to delete "all but one row" from that table too, or is it all rows that fit certain criteria? Or am I getting the tables backwards?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 23, 2009 at 12:04 pm
Lynn Pettis (3/23/2009)
First question, does the following query return the correct result set that you are deleting items based on?
SELECT distinct
Application.ApplicationID,
ApplicationProduct.ProductControlID
FROM
[Application]
INNER JOIN [ApplicationProduct]
ON Application.ApplicationID = ApplicationProduct.ApplicationID
INNER JOIN [Product]
ON ApplicationProduct.ProductControlID = Product.ProductControlID
INNER JOIN NapaThirdParty
ON NapaThirdParty.PN = Product.PartNumber
WHERE
(Product.ProductLineID = 10290)
yes the following query has the resultset of all the applicationid save one that I need to have deleted.
If you want the other 2 tables here they are:
USE [PartsTrakStaging]
GO
/****** Object: Table [dbo].[NapaThirdParty] Script Date: 03/23/2009 12:03:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[NapaThirdParty](
[AAIA ID] [int] NULL,
[Description] [varchar](50) NULL,
[PN] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [PartsTrakStaging]
GO
/****** Object: Table [dbo].[Product] Script Date: 03/23/2009 12:03:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Product](
[ProductID] [int] IDENTITY(1,1) NOT NULL,
[ProductLineID] [int] NOT NULL,
[PartNumber] [nvarchar](50) NOT NULL,
[SearchNumber] [nvarchar](50) NOT NULL,
[ProductNumber] [nvarchar](50) NULL,
[ProductStatusID] [int] NOT NULL,
[DateStatusChange] [smalldatetime] NULL,
[Publish] [bit] NULL,
[ProductControlID] [int] NULL,
[NumberRequired] [smallint] NOT NULL,
[BasePrice] [money] NULL,
[MPG] [int] NULL,
[UPC] [varchar](50) NULL,
[PolkFits] [int] NULL,
[PolkHits] [int] NULL,
[Rank] [int] NULL,
[Code] [char](1) NULL,
[DefaultUnitID] [int] NULL,
[ChangedDate] [datetime] NULL,
[ChangedType] [char](1) NULL,
[Transmit] [int] NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_Product_1] UNIQUE NONCLUSTERED
(
[ProductLineID] ASC,
[PartNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Product] WITH NOCHECK ADD CONSTRAINT [FK_Product_ProductControl] FOREIGN KEY([ProductControlID])
REFERENCES [dbo].[ProductControl] ([ProductControlID])
GO
ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_Product_ProductControl]
GO
ALTER TABLE [dbo].[Product] WITH NOCHECK ADD CONSTRAINT [FK_Product_ProductLine] FOREIGN KEY([ProductLineID])
REFERENCES [dbo].[ProductLine] ([ProductLineID])
GO
ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_Product_ProductLine]
GO
ALTER TABLE [dbo].[Product] WITH NOCHECK ADD CONSTRAINT [FK_Product_ProductStatus] FOREIGN KEY([ProductStatusID])
REFERENCES [dbo].[ProductStatus] ([ProductStatusID])
GO
ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_Product_ProductStatus]
GO
March 23, 2009 at 12:11 pm
Good, but still lacking. Some sample data and expected results would be the icing on the cake. That would allow us to validate our code before posting back.
Please help us help you.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply