Search code examples
functiont-sqlsplitssms

Update values from next or previous records in T-SQL

I have the following table in T-SQL: Table1:

enter image description here

I need to update the -1 values in [DrugClassificationFK] field, to the non -1 values, according to [MedicationCategory] gruppping

So, for example, for "fluticasone" MedicationCategory all the "-1" records should be also equal 98 as the record where WHID = 1182

This is what I need to see as a result: enter image description here

Is there any way to do it in T-SQL (in SSMS)?

(in Access, I could split this table in two, by "-1" and non "-1" values and then update the original table, with non "-1" new tbl, joining on [MedicationCategory])

More elegant way of doing it?

Thank you...

Solution 
  • UPDATE Z
SET Z.DRUGCLASSIFICATIONFK=S.MAX_DRUGC
FROM MedicationCategory Z
INNER JOIN(SELECT MedicationCategory,MAX(DRUGCLASSIFICATIONFK) AS MAX_DRUGC
FROM TABLE1
GROUP BY MedicationCategory) S ON S.MedicationCategory=M.MedicationCategory

    Hope this Query Works for your case:

    Note: Untested