Update field problem
-
Hi,
I have a table SUBPARTS with a ProdID, SubPartID, SubpartDesc, SubPartSerial. The data looks like this:
10336 90 CCX Emulation 1253A
10336 91 Elan Card 1121A
10336 92 Herbie 1043A
10336 93 SOUNDCARD 61506
I have a second table PC that has a ProdID, PC_SubpartDesc1, PC_SubpartDesc2, PC_SubpartDesc3, PC_SubpartDesc4 with the matching serial numbers1 thru 4.
I need to do an update statement where I update the 4 SubPartDesc in the fields PC_SubpartDesc1 thru PC_SubpartDesc4 where the ProdID = 10336. The list of ProdID's may have 1 up to 4 SupPartDesc's. I know this is reverse normalization, but its what I need. Any suggestions would be appreciated.
Thanks
David
-
Does the table PC have a column with the serial numbers?
I have a second table PC that has a ProdID, PC_SubpartDesc1, PC_SubpartDesc2, PC_SubpartDesc3, PC_SubpartDesc4
>> with the matching serial numbers1 thru 4. <<
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
If the serial number is not an issue, this works.
-----------
declare @I int, @rowcount int, @ctr int, @prod varchar(50), @lastprod varchar(50)
select
identity(int, 1, 1) as nDex,
1 as seq,
prodid,
subpartid,
subpartdesc,
subpartserial
into
#tmp
from
subparts
order by
prodid,
subpartid
set @I = 1
set @ctr = 1
select @rowcount = count(*) from #tmp
select @lastprod = prodid from #tmp where seq = 1
while @I <= @rowcount
begin
select @prod = ProdID from #tmp where nDex = @I
if @prod = @lastprod
begin
set @ctr = @ctr + 1
update #tmp
set seq = @ctr
where nDex = @I
end
else
set @ctr = 1
select @lastprod = prodid from #tmp where nDex = @I
set @I = @I + 1
if @ctr = 5
set @ctr = 1
end
update pc
set pc_subpartdesc1 = t1.subpartdesc
from #tmp t1
where t1.prodid = pc.prodid and t1.seq = 1
update pc
set pc_subpartdesc2 = t2.subpartdesc
from #tmp t2
where t2.prodid = pc.prodid and t2.seq = 2
update pc
set pc_subpartdesc3 = t3.subpartdesc
from #tmp t3
where t3.prodid = pc.prodid and t3.seq = 3
update pc
set pc_subpartdesc4 = t4.subpartdesc
from #tmp t4
where t4.prodid = pc.prodid and t4.seq = 4
drop table #tmp
--------
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
Give this a try...
IF OBJECT_ID('tempdb..#Wrk') IS NOT NULL
DROP TABLE #Wrk
IF OBJECT_ID('tempdb..#SubParts') IS NOT NULL
DROP TABLE #SubParts
CREATE TABLE #SubParts
(
ProdID int
, SubPartID int
, SubPartDesc varchar(50)
, SubPartSerial varchar(20)
)
INSERT INTO #SubParts
Values(10336,90,'CCX Emulation','1253A')
INSERT INTO #SubParts
Values(10336,91,'Elan Card','1121A')
INSERT INTO #SubParts
Values(10336,92,'Herbie','1043A')
INSERT INTO #SubParts
Values(10336,93,'SoundCard','61506')
SELECT
PRODID
, PC_SubPartDesc1 = CASE RIGHT(SubPartID,1)
WHEN 0 THEN SubPartDesc
ELSE NULL
END
, PC_SubPartDesc2 = CASE RIGHT(SubPartID,1)
WHEN 1 THEN SubPartDesc
ELSE NULL
END
, PC_SubPartDesc3 = CASE RIGHT(SubPartID,1)
WHEN 2 THEN SubPartDesc
ELSE NULL
END
, PC_SubPartDesc4 = CASE RIGHT(SubPartID,1)
WHEN 3 THEN SubPartDesc
ELSE NULL
END
, SubPartSerial
INTO #Wrk
FROM #SubParts
SELECT * FROM #Wrk WHERE PC_SubPartDesc1 IS NOT NULL
SELECT * FROM #Wrk WHERE PC_SubPartDesc2 IS NOT NULL
SELECT * FROM #Wrk WHERE PC_SubPartDesc3 IS NOT NULL
SELECT * FROM #Wrk WHERE PC_SubPartDesc4 IS NOT NULL
You should be able to write the update statements from there.
Gary Johnson
Sr Database Engineer -
The 4 serial number fields are matches to the 4 part number fields. So it also has PC_SubpartSN1, PC_SubpartSN2, PC_SubpartSN3, PC_SubpartSN4. So the serial numbers have to match the corresponding part descriptions. The statement above does not work because the SubPartID is a identity field that is just an ongoing number. The sample I gave just had the 90,91,92,93 as an example.
Thanks
-
If you are using 2005 you would be able to use the new Rank feature to do your cross tab rather than my Right function.
Somewhere on this site there is an Update statement that will do rankings for 7.0 and 2000 users. It works very well and you could adopt it for this use and then do the cross tab on it.
Good luck!
Gary Johnson
Sr Database Engineer -
I modified Jasons response above to solve this problem. I added a seq number in the PC table and get the sequence number. Then I did a join on the PC Table to the SubParts table on the ProdID. I did an update statement usin the Seq number and running it with a 1, then 2 etc to update the PC_SubPartDescription1 and SubPartSerialNumber1. This worked and I got the data I needed into the table. Thanks Jason and Gary for your answers.
Dave
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply