update not working ? - 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

update not working ?

Author

Topic

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2014-08-06 : 06:27:25

am i thinking of this the right way around
i want to update BNYWorkingDataloadFile from the dbo.BNYReport262
where securityId = masteridentifier
where dbo.BNYReport262.AssetType = 'Future-Bond' or dbo.BNYReport262.AssetType = 'Future-InterestRate

its not updating as expected


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

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-08-06 : 06:30:47

are securityid and MasterIdentifier common columns?

Javeed Ahmed

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2014-08-06 : 06:37:43

ya they are common columns same data in both thats why they want to match it on that. its more is my query right. im just not sure if im thinking about it back wards.

i had 1 Future-Bond in BNYReport262 but it updated 20 rows in dbo.BNYWorkingDataloadFile.

maybe its the files as they are test files so i was just wondering if i did my query right

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-08-06 : 07:11:36

You're query should work

The NotionalAmount should be updated to MVBookNotional wherever the SecurityID is equal to MasterIdentifier between the two tables for riows in the BNYReport262 that are either 'Future-Bond' Or 'Future-InterestRate'. No other records where the SecurityID is equal to MasterIdentifier between the two tables will be updated.

UPDATE dbo.BNYWorkingDataloadFile
SET NotionalAmount = A.MVBookNotional
FROM dbo.BNYReport262 A
INNER JOIN dbo.BNYWorkingDataloadFile B ON A.SecurityID = B.MasterIdentifier
WHERE A.AssetType IN ('Future-Bond','Future-InterestRate')

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2014-08-06 : 07:15:59

perfect thanks just wanted to make sure

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources