Solved

sql... if then update

Posted on 2013-01-11
5
286 Views
Last Modified: 2013-01-11
Hi Guys,

I am trying to say if [Clearing_Broker] = 'one'
then
run this update
	UPDATE dbo.DailyPositions_JEFF
	SET [Jeff_Multiplier] = 
	(SELECT dbo.MarketsDB.[JEFF_MULTIPLIER]
	FROM dbo.MarketsDB
	INNER JOIN dbo.AccountsDB
	ON dbo.DailyPositions_JEFF.Account_ID = dbo.AccountsDB.Account_ID
	WHERE [JEFF_SYMBOL]= dbo.DailyPositions_JEFF.[Product]
	AND [Clearing_Broker] = 'one'
	)

Open in new window


else

run this update
	UPDATE dbo.DailyPositions_JEFF
	SET [Jeff_Multiplier] = 
	(SELECT dbo.MarketsDB.[UBS_MULTIPLIER]
	FROM dbo.MarketsDB
	INNER JOIN dbo.AccountsDB
	ON dbo.DailyPositions_JEFF.Account_ID = dbo.AccountsDB.Account_ID
	WHERE [UBS_SYMBOL]= dbo.DailyPositions_JEFF.[Product]
	AND [Clearing_Broker] = 'two'
	)

Open in new window



can anyone help me combine this into a if then run update 1, else run update 2

thanks!!
0
Comment
Question by:solarissf
  • 3
5 Comments
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 38767964
UPDATE dp

SET [Jeff_Multiplier] = case when [Clearing_Broker] = 'one' then mdb2.[JEFF_MULTIPLIER]
else mdb.[UBS_MULTIPLIER] end

from

 dbo.DailyPositions_JEFF dp
join dbo.MarketsDB mdb on mdb.[UBS_SYMBOL]= dp.[Product]
join dbo.MarketsDB mdb2 on mdb2.[JEFF_SYMBOL]= dp.[Product]
join dbo.AccountsDB adb on dp.Account_ID = adb.Account_ID
0
 

Author Comment

by:solarissf
ID: 38767985
sorry, I dont understand this.  Do I paste the whole thing in?  when I do I get errors on the inner join
0
 
LVL 32

Accepted Solution

by:
ewangoya earned 500 total points
ID: 38767988
try this
UPDATE dbo.DailyPositions_JEFF
	SET [Jeff_Multiplier] = 
	(SELECT 
		case 
			when [Clearing_Broker] = 'one' then 
				dbo.MarketsDB.[JEFF_MULTIPLIER]
			when [Clearing_Broker] = 'two'then	
				dbo.MarketsDB.[UBS_MULTIPLIER]
		end
	FROM dbo.MarketsDB
	INNER JOIN dbo.AccountsDB
	ON dbo.DailyPositions_JEFF.Account_ID = dbo.AccountsDB.Account_ID
	WHERE ([JEFF_SYMBOL]= dbo.DailyPositions_JEFF.[Product]
			AND [Clearing_Broker] = 'one')
	OR ([UBS_SYMBOL]= dbo.DailyPositions_JEFF.[Product]
			AND [Clearing_Broker] = 'two')
	)

Open in new window

0
 

Author Comment

by:solarissf
ID: 38768023
AWESOME... WORKS

THANKS SO MUCH
0
 

Author Comment

by:solarissf
ID: 38768842
Okay, last one.  And if I need to put in a new post I will.  This will be in a stored procedure
same concept as if then else...  but i dont think it can be a case

This is my original.

UPDATE dbo.DailyPositions_JEFF
SET [P&L Settlement]= (((ISNULL([Delta Settlement],0)*[Value 1Point])*[Local Rate])*[Qty_Net])

Open in new window


I want to say...
If [Value 1Point] = 0.9999
Then
do this
SET [p&L Settlement] =
[delta settlement] *
(1/[tick_size]) *[tick_value]
*
[local rate]

else.... do this

SET [P&L Settlement]= (((ISNULL([Delta Settlement],0)*[Value 1Point])*[Local Rate])*[Qty_Net])



thanks for all the help
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now