Update query ran in Access but not in SQL server

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

Transact-SQL (2005)

Update query ran in Access but not in SQL server

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2009-07-23 : 10:58:04

There are two tables tOrderFinal and tOrderYear. There is no relationship between them.
Below is the data in each table.
tOrderFinal
AmountA AmountB FinalAmount
500 50
600 60
700 70
800 80

tOrderYear
OrderYear
2006

(OrderYear will be changed based on user’s input.)

In Access, I used one update query to get FinalAmount. It works fine.

UPDATE tOrderYear, tOrderFinal SET tOrderFinal.FinalAmount = IIf([OrderYear]="2005",[AmountA],([AmountA]-[AmountB]));

Now, it needs to run in SQL server. I use the code below but did not work.

UPDATE tOrderFinal SET tOrderFinal.FinalAmount =
case
when [tOrderYear].[OrderYear]='2005' then [AmountA]
else ([AmountA]-[AmountB])
end

How to make it run in SQL server?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-23 : 11:00:22

UPDATE t SET FinalAmount =
case
when [OrderYear]='2005' then [AmountA]
else ([AmountA]-[AmountB])
end
from tOrderFinal as t

Madhivanan

Failing to plan is Planning to fail

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2009-07-23 : 11:03:32

I tested it but got error:
Invalid column name 'OrderYear'.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-23 : 11:10:35

Something like

UPDATE F SET F.FinalAmount =
case
when Y.OrderYear='2005' then [AmountA]
else ([AmountA]-[AmountB])
end
from tOrderFinal as F inner join tOrderYear as Y
on F.key_col=Y.key_col

Madhivanan

Failing to plan is Planning to fail

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2009-07-23 : 11:23:48

You can not join these two tables because there is no column to join.(no relationship between them)

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2009-07-23 : 12:30:16

Someone help me. It works now.

UPDATE tOrderFinal
SET FinalAmount = CASE
WHEN EXISTS(SELECT * FROM tOrderYear
WHERE OrderYear = '2005')
THEN AmountA
ELSE AmountA - AmountB
END;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources