asked
Update Query
I need to modify this query to only update one record. This is called from a screen built in c# and when an item is entered in a screen , there is a save button which runs the procedure. so if there are 10 items in the price type and the user adds the 11th item in and hits save, this query replaces the price and all other information with the 11th item's price, date, username etc...it should just be isolated to the item and price_typ_id and it's not can you assist in helping me understand whet needs to be fixed? BEGIN UPDATE dbo.T_PRC_WRK SET PRC = ROUND(@PRC,2), PRC_EFF_DT = @PRC_EFF_DT, PRC_END_DT = @PRC_END_DT, APPROVE_TS = CASE WHEN @APPROVED = 1 AND APPROVE_TS IS NULL THEN GETDATE() ELSE NULL END, APPROVE_BY = CASE WHEN @APPROVED = 1 AND APPROVE_TS IS NULL THEN RIGHT(SYSTEM_USER,7) ELSE NULL END, APPROVED = @APPROVED, CMNT_DSCR = @CMNT_DSCR WHERE PRC_TYP_ID = @PRC_TYP_ID AND EM_ITEM_NUM = @EM_ITEM_NUM --ADDED 11/23/2010 OR (PRC @PRC OR PRC_EFF_DT @PRC_EFF_DT OR PRC_END_DT @PRC_END_DT OR APPROVED @APPROVED OR CMNT_DSCR @CMNT_DSCR ) END
Did this happen to break sometime around 11/23/2010? To only update based on type and item, you might change the first `OR` to and `AND`. With an `OR` there, it will update any row where prc @PRC not just the row you want. If you change it to `AND`, it will only update the specific row based on type and item when any of the `OR` conditions are true. WHERE prc_typ_id = @PRC_TYP_ID AND em_item_num = @EM_ITEM_NUM --ADDED 11/23/2010 AND ( prc @PRC OR prc_eff_dt @PRC_EFF_DT OR prc_end_dt @PRC_END_DT OR approved @APPROVED OR cmnt_dscr @CMNT_DSCR )
this was added on 11/23 AND ( prc @PRC OR prc_eff_dt @PRC_EFF_DT OR prc_end_dt @PRC_END_DT OR approved @APPROVED OR cmnt_dscr @CMNT_DSCR ) But when any ont of those attributes change I need to update the table. Instead - when it sees that one of those attributes have changed - it updates the changed attributes and not related to the item number
