update query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

update query

Author

Topic

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-09-21 : 14:40:39

i have the following script where i would like it to update the columns _BCP and ABAC

CREATE TABLE #FINAL
( ACTIONCODE VARCHAR (24),
ROLECODE VARCHAR(4),
FUNDCODE VARCHAR(4),
_BCP VARCHAR(24),
ABAC VARCHAR(24))

-- GET ALL FUNDS ---

SELECT DISTINCT
R.ACTIONCODE,
R.ROLECODE,
R.FUNDCODE
INTO #FUND
from dbo.RoleAction R
where R.FundCode IN ('_BCP','ABAC')
GROUP BY
R.ACTIONCODE,
R.ROLECODE,
R.FUNDCODE

--Add all data to the final table
INSERT #Final
( ACTIONCODE,
ROLECODE,
FUNDCODE)
SELECT DISTINCT
ACTIONCODE,
ROLECODE,
FUNDCODE
FROM #FUND

-- Update final table
UPDATE #Final
SET _BCP = R.ROLECODE
FROM #fund r, #Final f
WHERE f.FUNDCODE = r.FUNDCODE and f.ACTIONCODE = r.ACTIONCODE
AND r.FUNDCODE = '_BCP'

UPDATE #Final
SET ABAC = R.ROLECODE
FROM #fund r, #Final f
WHERE f.FUNDCODE = r.FUNDCODE and f.ACTIONCODE = r.ACTIONCODE
AND r.FUNDCODE = 'ABAC'
--Final select

select distinct
ACTIONCODE,
_BCP,
ABAC
from #FINAL

group by
ACTIONCODE,
_BCP,
ABAC

--DROP TABLE #FUND
--DROP TABLE #Final

i keep getting duplicated actioncodes

ACTIONCODE --------_BCP ------ABAC
AddCashAccounts -----NULL -------FR
AddCashAccounts -----ITA -------NULL
what i would like to see is

ACTIONCODE --------_BCP-------- ABAC
AddCashAccounts --------ITA ------ FR

any idea how to accomplish this. i have written successful update scripts to

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-21 : 22:31:54

that means the insert itself has inserted multiple records per ACTIONCODE in #Final. can you post what below query returns?


SELECT DISTINCT
ACTIONCODE,
ROLECODE,
FUNDCODE
FROM #FUND

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-09-22 : 08:25:48

these are the results

each fund can have multiple actions.
each action can exist in multiple funds

ACTIONCODE --------ROLECODE -------FUNDCODE
AddCashAccounts ---------FR ------- _BCP
AddCashAccounts ---------FR ------- ABAC
AddCashAccounts ---------ITA -------- _BCP
AddCashAccounts ---------ITA -------- ABAC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-22 : 12:09:21

you want ROLECODE and FUNDCODE side by side with pivoted values? (_BCP,ABAC etc)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-09-22 : 12:40:05

yes

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-22 : 13:07:39

what significance does it make?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-22 : 13:09:10

also provided you group by ROLECODE,FUNDCODE which have more than one distinct combination for each ACTIONCODE then how do you think you can reduce them to single record? thats not possible if you want to return ROLECODE,FUNDCODE along with pivoted values.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-09-22 : 14:11:38

i actually got it to work they way i want to see it,

ACTIONCODE _BCP ABAC
AddCashAccounts ITA FR
AddCounterparty TDM ITA
AddEditBroker ID TDM
AddNewDeal SET ITA
AddServicer ITA TDM
how i was able to accomplish this was to remove some joins in my update statment

now i just would like to be able to put in the column multiple values

so for _BCP AddCashAccounts should show in _BCP column
ITA,FR, EO. multiple permissions for code and action.

is there a way to do that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-22 : 22:48:11

sorry can you post how the output would be in that case?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources