Ambiguous column name - 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

General SQL Server Forums

New to SQL Server Programming

Ambiguous column name

Author

Topic

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2014-08-05 : 08:49:04

hi
i have the following code



;With MyUpdates
AS
(
Select *
from dbo.BNYReport262
where AssetType = 'Future-Bond' 
)

UPDATE dbo.BNYWorkingDataloadFile
SET NotionalAmount = MVBookNotional
FROM dbo.BNYReport262 A
INNER JOIN MyUpdates B ON A.SecurityID = B.SecurityID

but getting the following error
Ambiguous column name 'MVBookNotional'.

why would i get this

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-08-05 : 08:57:53

It means your column 'MVBookNotional' is in both tables and you need to qualify which table alias you will pull from A or B. I would guess B

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2014-08-05 : 09:25:28

if i just want to update 1 column from 1 table with another column from another can i just use something as simple as this
[code]
UPDATE dbo.BNYWorkingDataloadFile
SET NotionalAmount = MVBookNotional
FROM dbo.BNYReport262
where SecurityID = MasterIdentifier
AND AssetType = 'Future-Bond'
[\code]

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-08-05 : 09:29:38

you need to join the other table in your query to pull the column with which you want to update the other table

Javeed Ahmed

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-08-05 : 09:41:18

;With MyUpdates
AS
(
Select *
from dbo.BNYReport262
where AssetType = 'Future-Bond'
)

UPDATE dbo.BNYWorkingDataloadFile
SET NotionalAmount = B.MVBookNotional
FROM dbo.BNYReport262 A
INNER JOIN MyUpdates B ON A.SecurityID = B.SecurityID

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-08-05 : 09:44:13

You are using different tables - -did't notice that .. you will want to join to your target table as ahmeds08 is stating

;With MyUpdates
AS
(
Select *
from dbo.BNYReport262
where AssetType = 'Future-Bond'
)

UPDATE dbo.BNYWorkingDataloadFile
SET NotionalAmount = B.MVBookNotional
FROM dbo.BNYReport262 A -- don't know why this is involved as you are pulling from the CTE above -- all you should need to do is join on the target
INNER JOIN MyUpdates B ON A.SecurityID = B.SecurityID
INNER JOIN BNYWorkingDataloadFile C ON A.SecurityID = C.SecurityID

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2014-08-05 : 11:27:55

should this do the job to cover this

I. Filter records where Asset Type = ‘Future-Bond’ AND/OR ‘Future-InterestRate’
II. Perform identity match on MasterIdentifer (from Holdings table) to Security ID on this file
III. Populated Holdings.NotionalAmount with MV Book Notional from Report 262


UPDATE dbo.BNYWorkingDataloadFile 
SET dbo.BNYWorkingDataloadFile.NotionalAmount = dbo.BNYReport262.MVBookNotional
FROM dbo.BNYWorkingDataloadFile 
INNER JOIN dbo.BNYReport262 ON dbo.BNYWorkingDataloadFile.MasterIdentifier = dbo.BNYReport262.SecurityID
where dbo.BNYWorkingDataloadFile.InvestmentType = 'Future-Bond' or dbo.BNYWorkingDataloadFile.InvestmentType = 'Future-InterestRate'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources