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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply