CODEWITHSUNDEEP
sqlsql-server
Fadhl
Fadhl

Reputation: 21

Update only if Existing value is less than new value

I am trying to make this code update the existing [Price] ONLY when the i.[Price] is greater than the existing. I would appreciate your help.

MyDatabase set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO --This trigger is attached to the Price_Import Table so when this table gets new price values the trigger will update the price of such items in the Mydatabase MenuItem Table which is the main item table in Mydatabase

     ALTER TRIGGER [dbo].[trgafterinsertinto_Price_Import_Table] ON [dbo].[Price_Import]
AFTER INSERT
    AS 
    BEGIN
    SET NOCOUNT ON;

     UPDATE [dbo].[MenuItem] 
SET [Price] = i.[Price] ,[Cost] = i.[Cost]
    FROM Inserted i
   WHERE MenuItem.PLU = i.PLU
   DELETE FROM dbo.Price_Import
   END

This code update both Cost and Price but I need help modifying it to only update price when the new price is greater than the old.

Upvotes: 1

Views: 3148

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

Assuming you mean you want to update the row only when the condition is met, just add in that condition, either in the on clause or where clause:

UPDATE mi
    SET Price = i.Price,
        Cost = i.Cost
    FROM [dbo].[MenuItem] mi JOIN
         Inserted i
         on mi.PLU = i.PLU AND i.Price > mi.Price;

If you really mean price only, then use case instead:

UPDATE mi
    SET Price = (CASE WHEN i.Price > mi.Price THEN i.Price ELSE mi.Price END)
        Cost = i.Cost
    FROM [dbo].[MenuItem] mi JOIN
         Inserted i
         on mi.PLU = i.PLU;

Upvotes: 2

AntJavaDev
AntJavaDev

Reputation: 1262

you might need something like this stackUpdateHaving

just put a having clause into your where clause like : HAVING m.val > m.incVal

Upvotes: 0

Related Questions