| Author |
Topic |
|
Lumbago
Norsk Yak Master
3271 Posts |
 Posted - 2004-09-10 : 06:42:38
|
| I need to update two money-fields in one of my tables where the second value is always the same as the first but in a different currency. Can I do like this:UPDATE table SET Volume = SUM(Something), Volume_euro = Volume * @RateFROM ....or must I do like this:UPDATE table SET Volume = SUM(Something), Volume_euro = SUM(Something) * @RateFROM ....Or doesn't it matter? The SUM(Something) is actually a CASE-statement with 6 different options...--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-09-10 : 07:22:50
|
| You'd have to do the second option, either that or do two update statements... |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-09-10 : 07:52:19
|
| Actually neither will work.Try it with a temp table to get the syntax right.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-09-10 : 08:01:28
|
| Umh...what...? I'm using option 2 as we speak and it's working like charm...I'm just asking because it would make my code shorter and more readable, I'll post the full statement if you like...--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-09-10 : 08:14:27
|
| To clarify, the first statement won't work as aliased column names cannot be used within the same query as the optimiser cannot work out what you are refering to at the point of compiling the query... The second option doesn't use the alias so will work no problem... |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-09-10 : 08:19:12
|
| You're confusing me here, where are the alias?? "Volume" and "Volume_euro" are both reald fieldnames in the table "table" (which naturally has another name). What I saw as the problem with this is that the update to Volume_euro actually has to happen *after* Volume has been updated to give the right result. I guess a simple test would be appropriate here...--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-09-10 : 08:23:01
|
| I guess I should have guessed this by myself, the following test shows that option 1 doesn't work at all:DECLARE @myTable table (Volume int, Volume_euro int)INSERT INTO @myTable SELECT 100, 200SELECT * FROM @myTableUPDATE @myTable SET Volume = 200, Volume_euro = Volume * 2SELECT * FROM @myTable--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-09-10 : 08:23:30
|
| Sorry, I got that wrong...You cannot use the first statement due to the fact that you cannot be sure which update will happen first... |
|
|
|
|
Simple update-question