October 19, 2009 at 3:25 pm
Hello All,
Before I get started, I want you to know I have JUST started up with SQL Server 2000 here at work so bear with me. =)
Say I have a table called "myTable" in a database called "myDb".
I have 3 columns in this table, named "A", "B", and "C".
Columns "A" and "B" all have values placed there by another query that runs first.
I would like to calculate what appears in "C" on the fly.
If A - B < 0 I want C to equal A - B
otherwise set C equal to 0.
Below you'll find my code... I've tried so many different combinations of brackets and what-not and nothing seems to work. It seems to be catching up on the IF statement when I run the query analyzer thing.
SELECT *
FROM myDb.[myTable]
IF [A] - < 0
BEGIN
UPDATE myTable
SET C = [A] -
END
ELSE
BEGIN
UPDATE myTable
SET C = 0
END
My hunch is that I can't use the less than operator this way because I might be "grabbing" the entire column of A and B... I want to just check the current row.
Any assistance with this would be great appreciated.
Thanks,
Max
October 19, 2009 at 3:54 pm
Try this:
UPDATE myDB.dbo.myTable
SET C = CASE WHEN [A]- < 0 THEN ([A]-) ELSE 0 END
WHERE <your conditions if any;this line is optional>
Note that if you don't put the where clause all rows will be updated. Does that make sense?
HTH,
Supriya
October 19, 2009 at 4:20 pm
Hi,
I'll try this first thing tomorrow and I'll get back to you.
Thanks for the repsponse!
Max
October 19, 2009 at 5:10 pm
You should be able to make that case statement a calculated field so that you don't need to actually update it. The field is automatically updated as soon as you update a or b.
Something like this (Pardon, it's from memory, not on a SQL server ATM)
CREATE TABLE dbo.MyTable(
A int,
B int,
C (CASE WHEN [A]- < 0 THEN ([A]-) ELSE 0 END)
)
October 20, 2009 at 2:43 pm
Thanks for the help guys! We got it to work!
Max
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply