updating one colum with another from another table

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 2012 Forums

Transact-SQL (2012)

updating one colum with another from another table

Author

Topic

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2014-01-28 : 11:07:17

hi
i need to do the following
YieldDate with Bloomberg ISSUE_DT if not equal to {1901/01/01} AND not empty, else DATED_DT for positions, NAV_DAY_DT for forward transactions from Freg file

so i need to update yielddate in table 1 with issue_dt if issue date is equal to 19/01/01 and is not empty else pick dated_dt

so i have this much done

Update dbo.SSCIREWorkingDataloadFile
set YieldDate = issue_dt
FROM dbo.SSCIREWorkingBloombergHoldingsTable
where issue_dt = '19/01/01' or issue_dt !=''

if that is right how do i finish it off basically need the else part on

thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-01-28 : 11:18:36

How do the two tables relate? If you where to join them, what column(s) would you use? Also, how does DATED_DT and NAV_DAY_DT play a part in the update?

Below are some links that can help you provide more detail in your question, like sample data and expected output. If you follow those guidelines you'll get much better help and, more importantly, code that works.

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2014-01-28 : 11:27:40

the table arent related as such there is nothing linking them really. i didnt set up the tables.

basically if issue date is blank it updates yeilddate with the dated_DT
instead of issue date

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-01-28 : 11:48:21

Sorry, but that doesn't make any sense, unless there is one row in the dbo.SSCIREWorkingBloombergHoldingsTable table. Without more information I'm not sure how to help.

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2014-01-28 : 12:02:24

the two tables are SSCIREWorkingDataloadFile and SSCIREWorkingBloombergHoldingsTable and the will both have the same asset_ID column that matches them up

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-01-28 : 13:14:48

Assuming there is a one-to-one relationship between those tables:

UPDATE 
	DataLoad
SET 
	YieldDate = Holding.issue_dt
FROM 
	dbo.SSCIREWorkingDataloadFile AS DataLoad
INNER JOIN
	dbo.SSCIREWorkingBloombergHoldingsTable  AS Holding
	ON DataLoad.Asset_ID = Holding.Asset_ID
WHERE 
	Holding.issue_dt <> '19000101' 
	AND Holding.issue_dt <> ''

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2014-01-29 : 07:45:42

thanks that worked

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources