February 21, 2012 at 4:23 am
Hi All,
I was wondering if anyone could help, I'm trying to update a column in a table so if the price has changed in price column i would like it to update another column in the same table with 1 or 0
basically If the price < price then 1 else 0
so it will only be a 1 if the price has been lowered
I've tried
For Update
as
UPDATE <table> t1
SET pricechange = case when t1.price < i.price then 1
ELSE 0
END
FROM pricetable t1
INNER JOIN Inserted I ON I.ThisID = T1.ThisID
am i missing something? it does update but only to 1 value and doesn't update if the price is higher or lower it just comes out as 1.
February 21, 2012 at 4:36 am
You need to compare new value with previous one, which can be found in DELETED:
UPDATE t1
SET pricechange = case when D.price < i.price then 1 else 0 end
FROM pricetable t1
INNER JOIN Inserted I ON I.ThisID = T1.ThisID
INNER JOIN Deleted D ON D.ThisID = I.ThisID
BTW, why not to implement it as computed column...
February 21, 2012 at 4:44 am
That's worked great, didn't think of using computed column but i shall have a play around and see how that goes.
The above worked a treat, thanks for the quick reply
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy