Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update to Base Table from Details Table Expand / Collapse
Author
Message
Posted Thursday, July 11, 2013 6:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 3:52 AM
Points: 131, Visits: 248

Create Table #Customer_Profile_Master1
(
Lnno Varchar(15),
Co_apcode_1_categ Varchar(200),
Co_apcode_2_categ Varchar(200),
Co_apcode_3_categ Varchar(200),
Co_apcode_4_categ Varchar(200)
)

Insert Into #Customer_Profile_Master1(Lnno)
Values ('1')

Select * from #Customer_Profile_Master1


Drop table #Temp1
Create Table #Temp1
(
Lnno Varchar(15),
CustCode Varchar(200),
Co_apcode Varchar(200),
Category Varchar(200),
Rownumber Int
)




Insert Into #Temp1
Values ('1', 'M0000036', 'A0000026', 'SALARIED', 1),
('1', 'M0000036', 'A0000027', 'SELF EMPLOYED PROFESSIONALS', 2),
('1', 'M0000036', 'A0000028', 'STUDENT', 3)

Select * from #Temp1



My Requirement is I want to update in #Customer_Profile_Master1 table from #Temp1 as

Co_apcode_1_categ = For that Lnno if Rownumber = 1 then corresponding Category should be updated
Co_apcode_2_categ = For that Lnno if Rownumber = 2 then corresponding Category should be updated
Co_apcode_3_categ = For that Lnno if Rownumber = 3 then corresponding Category should be updated
Co_apcode_4_categ = For that Lnno if Rownumber = 4 then corresponding Category should be updated



I had tried this way but it is not working for me,

Update #Customer_Profile_Master1
Set Co_apcode_1_categ = Case when B.Rownumber = 1 then B.Category else '' End,
Co_apcode_2_categ = Case when B.Rownumber = 2 then B.Category else '' End,
Co_apcode_3_categ = Case when B.Rownumber = 3 then B.Category else '' End,
Co_apcode_4_categ = Case when B.Rownumber = 4 then B.Category else '' End
From #Customer_Profile_Master A Join #Temp1 B
On A.Lnno = B.Lnno



Please help me as I need this in urgent.

Thanks in Advance!
Post #1472525
Posted Thursday, July 11, 2013 6:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 7:59 AM
Points: 2,078, Visits: 2,410
I don't think this is the fastest solution, but it will work:
Select * from #Temp1
Select * from #Customer_Profile_Master1

Update #Customer_Profile_Master1
set Co_apcode_1_categ = (select top 1 Category from #temp1 where rownumber = 1 and cpm.lnno = lnno)
, Co_apcode_2_categ = (select top 1 Category from #temp1 where rownumber = 2 and cpm.lnno = lnno)
, Co_apcode_3_categ = (select top 1 Category from #temp1 where rownumber = 3 and cpm.lnno = lnno)
, Co_apcode_4_categ = (select top 1 Category from #temp1 where rownumber = 4 and cpm.lnno = lnno)
from #Customer_Profile_Master1 cpm

Select * from #Customer_Profile_Master1



** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1472534
Posted Thursday, July 11, 2013 6:28 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 7:59 AM
Points: 2,078, Visits: 2,410

Another solution is to PIVOT the #temp1 table and join this with the table you want to update

-- Pivot source table and join with table to update
Update #Customer_Profile_Master1
set Co_apcode_1_categ = [1]
, Co_apcode_2_categ = [2]
, Co_apcode_3_categ = [3]
, Co_apcode_4_categ = [4]
FROM (
SELECT lnno
, Category
, Rownumber
FROM #temp1
) AS SourceTable
PIVOT(MAX(Category) FOR Rownumber IN (
[1]
, [2]
, [3]
, [4]
)) AS PivotTable
inner join #Customer_Profile_Master1
on PivotTable.lnno = #Customer_Profile_Master1.lnno



** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1472544
Posted Thursday, July 11, 2013 6:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 3:52 AM
Points: 131, Visits: 248
Thanks a Lot!

Its worked for me!
Post #1472550
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse