November 8, 2011 at 7:39 am
Hi,
I have a requirement to dispaly product family
for that i have added one column with product family name in temporary table #rpt1 and we have product id
and created a temporary table #productdetails with two columns
pt_sp_type_c its a productfamily and pt_sb_type_c and its a productid
create table #rpt1
(
productIdINTNULL,
productfamily varchar(20) null
)
create table #productdetails
(
pt_sp_type_c
pb_sp_type_c
)
--inserted some data into productdetails
INSERT INTO #ProductDetails
SELECT DISTINCT pt_sp_type_c, pt_sb_type_c
FROM product..p_type_p_type
WHERE p_type_use_sp_c = 'RPCDB'
AND p_type_use_sb_c = 'TRD'
AND pt_rel_stat_c = 'ACTIVE'
and inserted null where ever its insert into #rpt1
and last updated the #rpt1
UPDATE #rpt1
SET Productfamily = PD.pt_sp_type_c
FROM #rpt1 s,
#ProductDetails PD
WHERE PD.pt_sb_type_c = CAST(s.productId AS VARCHAR(10))
actually in the req doc for the producttype column there is some data but when i execute teh above query for me its showing null
when i execute the snippet of code which is in insert into productdetails i got the data for that two columns i think there is some mistake in my
update statement,can we use update with righ too left outer joins..can any one come with proper solution.
November 8, 2011 at 8:17 am
Not exactly sure what the issue is here but your update looks pretty suspect to me. You have created a cross join between the two tables. You should use a join instead.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply