(URgent) My update statement is not working..

Hi..

I have inserted couple of data in a particular table which is as follows..

Portfolio Table

PortId PlanId PortfolioName PorfolioDescription ClientPortolioId

77117838BALPORT NULLNULL77217838HIGHGROW NULLNULL77317838MODGROW NULLNULL

My FundDBF is as follows

RowNumber FUND_ID f.ASSETDESC Import

20BALPORT Balanced True21MODGROW Moderate Growth True22HIGHGROW High Growth True

and this is my Update statement

UPDATE Statements..PlanPortfolioSETPlanId = pm.PlanId,PortfolioName = pm.FUND_ID, PortfolioDescription = pm.ASSETDESCFROMStatements..PlanPortfolio pJoin (SELECT DISTINCTp.PlanId,pd.FUND_ID,f.ASSETDESC ---pd.FUND_IDFROMPartDBF pdINNERJOIN Statements..ClientPlan pon pd.PLAN_NUM = p.ClientPlanIdINNERJOIN FundDBF fon pd.FUND_ID = f.FUND_IDWHERE pd.Import = 1AND NOT (pd.FUND_IDISNULLORLen(pd.FUND_ID) = 0ORpd.FUND_IDNOT IN (SELECTPortfolioNameFROMStatements..PlanPortfolio ppWherepp.PlanId = p.PlanId))) pmon p.PlanId = pm.PlanId

I am trying to put the above table with f,AssetDesc in the PorfolioDescription field..

Any help will be appreciated..

Regards

Karen

Can you provide the result of this query:

SELECT DISTINCT
p.PlanId,
pd.FUND_ID,
f.ASSETDESC ---pd.FUND_ID
FROM
PartDBF pd
INNERJOIN Statements..ClientPlan p
on pd.PLAN_NUM = p.ClientPlanId
INNERJOIN FundDBF f
on pd.FUND_ID = f.FUND_ID
WHERE
pd.Import = 1
AND NOT (
pd.FUND_IDISNULL
OR
Len(pd.FUND_ID) = 0
OR
pd.FUND_IDNOT IN (
SELECT
PortfolioName
FROM
Statements..PlanPortfolio pp
Where
pp.PlanId = p.PlanId
)
)

|||

Karen,

Not for nothin' but if those are real table and column names from your database, you are exposing a lot more than you should of what is obviously a very sensitive database.

|||

charles i have change the database Name.... and included dummy names...

|||

Dinakar,

when i ran that query the result set is empty..

Regards

Karen

|||

Sorry Karen, just making sure - I had a guy post a month ago who posted some stuff he shouldn't have and we wound up taking the thread down.

|||

so until the query I posted (you subquery) returns something your UPDATE will not work. Take the SELECT I posted and start with that..

|||

Dinakar ,

I updated the query bit to this

Update Statements..PlanPortfolioSET--SELECT Distinct PlanId = pd.PlanId, PortfolioName = pd.FUND_ID, PortfolioDescription = pd.ASSETDESCFROMStatements..PlanPortfolio pJoin (SELECT DISTINCTcp.PlanId,pd.FUND_ID,f.ASSETDESC--pd.FUND_IDFROMPartDBF pdINNERJOIN Statements..ClientPlan cpon pd.PLAN_NUM = cp.ClientPlanIdINNERJOIN FundDBF fon pd.FUND_ID = f.FUND_ID/*AND NOT (pd.FUND_ID IS NULLORLen(pd.FUND_ID) = 0OR *//*AND NOT EXISTS (SELECTPortfolioName--PortfolioDescriptionFROMStatements..PlanPortfolio ppWherepp.PlanId = cp.PlanId) */--)) pdon p.PlanId = pd.PlanId

and when i run the select distinct part i get this

17841 BALPORT Balanced
17841 HIGHGROW High Growth

17841 MODGROW Moderate

which is correct but rem out the Select distinct and use an update it doesnt do any thing...

I dont know whats going wrong

|||

You just need to update the desctription.

Update Statements..PlanPortfolio
SET
--SELECT Distinct
PlanId = pd.PlanId,
PortfolioName = pd.FUND_ID,
PortfolioDescription = pd.ASSETDESC

Also, what does this return:

SELECT DISTINCT
cp.PlanId,
pd.FUND_ID,
f.ASSETDESC--pd.FUND_ID
FROM
PartDBF pd
INNERJOIN Statements..ClientPlan cp
on pd.PLAN_NUM = cp.ClientPlanId
INNERJOIN FundDBF f
on pd.FUND_ID = f.FUND_ID

|||

The Select distinct returns

17841 BALPORT Balanced
17841 HIGHGROW High Growth
17841 MODGROW Moderate

|||

So your subquery is returning 3 rows for same planId of 17841? which is different from planid of 17838 in the outer SELECT ?

|||

actually it is right cause i have delete plan Id 17838 and inserted a new plan..|||

Dinakar,

The reason its not updating it because for every result returned in the Select Distinct query has a different portfolioId... so if i just update the portfolio desicription its gonna do that for all the descriptions who planId matches ...

so do u have any ideas..

Regards

Karen

|||

Run these queries again:

SELECT DISTINCT cp.PlanId,pd.FUND_ID,f.ASSETDESC--pd.FUND_IDFROM PartDBF pdINNERJOIN Statements..ClientPlan cpon pd.PLAN_NUM = cp.ClientPlanIdINNERJOIN FundDBF fon pd.FUND_ID = f.FUND_IDSELECT Distinct PlanId = pd.PlanId, PortfolioName = pd.FUND_ID, PortfolioDescription = pd.ASSETDESCFROM Statements..PlanPortfolio pJoin (SELECT DISTINCT cp.PlanId,pd.FUND_ID,f.ASSETDESC--pd.FUND_IDFROM PartDBF pdINNERJOIN Statements..ClientPlan cpon pd.PLAN_NUM = cp.ClientPlanIdINNERJOIN FundDBF fon pd.FUND_ID = f.FUND_ID) pdON p.PlanId = pd.PlanId

|||

Dinakar this is the result of both of my queries and they are the same

17842 BALPORT Balanced
17842 HIGHGROW High Growth
17842 MODGROW Moderate