Solved

sql... if then update

Posted on 2013-01-11
5
291 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 40

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:
Ephraim Wangoya 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server Shrink hurting performance? 4 49
Substring works but need to tweak it 14 35
Database Mail Profiles 1 52
HIghlights of SSIS? 3 44
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

739 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