March 24, 2009 at 8:20 am
declare @max-2 int
select @max-2 = max(productcontrolid) from productcontrol
select * from productcontrol where productcontrolid > @max-2 - 25
given the following query brings back 25unique numbers.
given
I need to update the productcontrolPN table with 1 of the unique numbers brought back in the query.
The table needing updated contains 25 rows all with different PN (numbers).
Any ideas?
USE [PartsTrakStaging]
GO
/****** Object: Table [dbo].[NapaThirdPartyPN] Script Date: 03/24/2009 08:18:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NapaThirdPartyPN](
[PN] [nvarchar](50) NOT NULL,
[pcid] [int] NULL,
CONSTRAINT [PK_NapaThirdPartyPN] PRIMARY KEY CLUSTERED
(
[PN] 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
USE [PartsTrakStaging]
GO
/****** Object: Table [dbo].[ProductControl] Script Date: 03/24/2009 07:59:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ProductControl](
[ProductControlID] [int] NOT NULL,
[Imagename] [varchar](50) NULL,
[ChangedDate] [datetime] NULL,
[ChangedType] [char](1) NULL,
CONSTRAINT [PK_Product Control] PRIMARY KEY CLUSTERED
(
[ProductControlID] 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
USE [PartsTrakStaging]
GO
/****** Object: Table [dbo].[NapaThirdParty] Script Date: 03/24/2009 08:00:13 ******/
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,
[app] [bit] NULL,
[pcid] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
March 24, 2009 at 8:28 am
Which one? Any one?
March 24, 2009 at 8:31 am
Steve Jones - Editor (3/24/2009)
Which one? Any one?
I need to update napathirdpartypn.pcid = (unique productcontrolid) from 1 of the 25.
March 24, 2009 at 8:39 am
pseudo code??
declare @max-2 int
select @max-2 = max(productcontrolid) from productcontrol
DT1 = select productcontrolid from productcontrol where productcontrolid > @max-2 - 25
dt2 = select pn from napathirdpartyPN
for (i = 0; i < 25; i++)
{
update napathirdpartypn set pcid = dt1.Rows["Productcontrolid"] where
pn = dt2.Rows["PN"]
}
March 24, 2009 at 8:40 am
Edit: Crosspost
DT1 = select productcontrolid from productcontrol where productcontrolid > @max-2 - 25
What controls the order? What makes a particular row row 1, not row 13?
Basically, what I'm asking is what's the relationship between the rows in productcontrol and the rows in napathirdpartyPN? What is it that controls which pn gets updated with what productcontrolid?
Bear in mind that in SQL, tables have no definied order and the order that data's returned from a query is undetermined unless there's an ORDER BY
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 24, 2009 at 9:08 am
being as sarcastic as possible, you may control the order if you wish. Maybe using order by??
I don't care, unique is unique and that's all that matters.
March 24, 2009 at 9:19 am
Use this:
declare @ID int;
--
select @ID = productcontrolid
from productcontrol
where productcontrolid in
(select top 25 productcontrolid
from productcontrol
order by productcontrolid desc);
That'll get you an ID you can use for your update.
Then just write whatever update you want to use for this.
Does that help?
- 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 24, 2009 at 9:20 am
foxjazz (3/24/2009)
being as sarcastic as possible, you may control the order if you wish. Maybe using order by??
Excuse me? I'm trying to help you here. Maybe you can take your snide comments elsewhere if you want some assistance.
I'm not asking if I can use an order by, I do know how to order rows.
I'm asking which of those unique 25 productIDs match to which of the napathirdpartyPN rows. Your pseudocode has a match based on row order (1..25), so, what is it that controls that row order in the two tables?
Is it the productcontrolid?
What determines which productcontrolid gets assigned to which row in the napathirdpartyPN table? ie what's the relationship between the two tables?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 24, 2009 at 9:23 am
The thing is, if you need one unique ID, why get 25? That seems like a waste of resources.
It seems as though you've described a portion of your problem, not the whole thing. If you need just one, grab the first one, @max-2 - 1. IF you aren't sure if one of those is unique, then use a query to find the one unique one and get that.
March 24, 2009 at 9:30 am
GSquared (3/24/2009)
Use this:
declare @ID int;
--
select @ID = productcontrolid
from productcontrol
where productcontrolid in
(select top 25 productcontrolid
from productcontrol
order by productcontrolid desc);
That'll get you an ID you can use for your update.
Then just write whatever update you want to use for this.
Does that help?
GSquared, I am unsure how this will help.
(it's not top 25 but I can adjust that stuff).
All I am trying to do is update a table and I sent pseudo code that would work just great.
Are you guys saying it can't be done. I don't understand why people are asking me for orderby.
That is why the bit of humor, not taken that way it seems.
It's fine, if it can't be done via a set, then it can't be done. Please just say that then.
March 24, 2009 at 9:35 am
If I write a cursor function to do this, would you guys understand it better?
March 24, 2009 at 9:38 am
foxjazz,
You post the DDL for your tables, which is good. What you also need to do is post the SQL code you are writing and having difficulty with, not psuedo code. Also, you really need to post sample data and expected results based on that sample data if you really want our help.
If you provide ALL of that, we can see what you are trying to do in your code and perhaps see what is wrong with the code. If we write our own code, by having sample data and expected results, we can also test and validate our code before posting it back for you to look at and test in your own development environment.
You have to help us help you. How can we help you if we have nothing to test against or with?
March 24, 2009 at 9:45 am
foxjazz (3/24/2009)
Are you guys saying it can't be done.
It can be done easily. It's a trivial problem. I'm just missing some information I need.
I don't know what your data looks like, so let's say that
DT1 = select productcontrolid from productcontrol where productcontrolid > @max-2 - 25
returns values 76..100
Is that a fair guess?
dt2 = select pn from napathirdpartyPN
This, I assume, is going to return a variety of different pn's from this table. Let me say that it returns the following values (and please feel free to correct this with what it actually does return)
aa
bb
cc
dd
ee
ff
gg
hh
ii
jj
...
yy
All I need to know is which of the numbers (76..100) match with which values of pn from the napathirdpartyPN table. Is there a foreign key relationship? Are they simply matched 1-1 by order (76 with aa, 77 with bb, 78 with cc, .... 100 with yy)? Is there some other match? Can I match at random (so it doesn't matter which value of productcontrolid matches to which value of pn)?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 24, 2009 at 9:51 am
GilaMonster (3/24/2009)
foxjazz (3/24/2009)
Are you guys saying it can't be done.It can be done easily. It's a trivial problem. I'm just missing some information I need.
I don't know what your data looks like, so let's say that
DT1 = select productcontrolid from productcontrol where productcontrolid > @max-2 - 25returns values 76..100
Is that a fair guess?
dt2 = select pn from napathirdpartyPNThis, I assume, is going to return a variety of different pn's from this table. Let me say that it returns the following values (and please feel free to correct this with what it actually does return)
aa
bb
cc
dd
ee
ff
gg
hh
ii
jj
...
yy
All I need to know to write a very short, very simple update is which of the numbers (76..100) match with which values of pn from the napathirdpartyPN table. Is there a foreign key relationship? Are they simply matched 1-1 by order (1 76 with aa, 77 with bb, 78 with cc, ....), is there some other match? Can I match at random (so it doesn't matter which value of productcontrolid matches to which value of pn)?
It's fine, if it can't be done via a set, then it can't be done. Please just say that then.
If it couldn't, I would say. It can, it's not difficult.
You have the gist of it.
My answer is it doesn't matter what the order is on anything. And you are correct in assuming what you have done.
The result set of the productcontrol query looks like this: (if it helps)
3621004
3621005
3621006
3621007
3621008
3621009
3621010
3621011
3621012
3621013
3621014
3621015
3621016
3621017
3621018
3621019
3621020
3621021
3621022
3621023
3621024
3621025
3621026
3621027
3621028
select * from napathirdpartypn results in
3191NULL
3193NULL
3340NULL
3391NULL
3392NULL
3394NULL
3400NULL
3420NULL
3482NULL
3518NULL
3595NULL
3646NULL
3687NULL
3688NULL
3695NULL
3699NULL
3700NULL
3703NULL
3704NULL
3708NULL
3738NULL
3741NULL
3760NULL
3800NULL
3900NULL
March 24, 2009 at 9:52 am
foxjazz (3/24/2009)
GSquared (3/24/2009)
Use this:
declare @ID int;
--
select @ID = productcontrolid
from productcontrol
where productcontrolid in
(select top 25 productcontrolid
from productcontrol
order by productcontrolid desc);
That'll get you an ID you can use for your update.
Then just write whatever update you want to use for this.
Does that help?
GSquared, I am unsure how this will help.
(it's not top 25 but I can adjust that stuff).
All I am trying to do is update a table and I sent pseudo code that would work just great.
Are you guys saying it can't be done. I don't understand why people are asking me for orderby.
That is why the bit of humor, not taken that way it seems.
It's fine, if it can't be done via a set, then it can't be done. Please just say that then.
I don't think you understand the code I posted.
What it does is select one of the top 25 product control IDs. That's what your code was doing with the @max-2 piece. Selecting one of them, doesn't matter which one, is what you said you wanted to do. What I posted does that.
If what you want isn't what you asked for, then ask for what you actually want.
- 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
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply