SQL query to update a record based on the max value in another column

user_816695 0
Viewed 332

The Problem

I have this table. (You can also view it in DBFiddle.)

Id Version Item No. Notes
1 NULL 31
2 1 31 tasty
3 2 31 kinda tasty
4 NULL 32
5 1 32 meh
6 2 32 alright
7 3 32 fabulous
8 NULL 33 ambivalent
9 1 33 gross
10 2 33 puke

The 1st column is the primary key. The 2nd and 3rd column are integers. The 4th column is a VARCHAR.

For every unique Item No. where its Version is NULL, I want to look at the record with the highest Version value, take the content of its Notes field, and copy it over.

This is much easier to understand visually; after the command is run, the table should look like this:

Id Version Item No. Notes
1 NULL 31 kinda tasty
2 1 31 tasty
3 2 31 kinda tasty
4 NULL 32 fabulous
5 1 32 meh
6 2 32 alright
7 3 32 fabulous
8 NULL 33 ambivalent
9 1 33 gross
10 2 33 puke

Explanation of the Changes

  • for Item No. 31, "kinda tasty" was copied over because it's in the record with the highest Version number, and the target cell is not occupied.
  • for Item No. 32, "fabulous" was copied over for the same reason.
  • "puke" was NOT copied over to replace "ambivalent", because the target cell is occupied.

The Question

What is the query to achieve this in SQL Server?

I know that I need a way of grouping records together by Item No., find the one with the highest Version value, take its Notes value, and copy it to the record where the Version is NULL, but I am having trouble translating this to SQL.

sql-update sql-server sql
4 Answers

Try this:

UPDATE t SET notes = (
   SELECT TOP 1 s.notes 
   FROM t s 
   WHERE s.item_no = t.item_no 
   ORDER BY version DESC
) WHERE version IS NULL AND notes IS NULL

Check demo

user_58768030

Use an updatable CTE:

WITH cte AS (
  SELECT *, FIRST_VALUE(Notes) OVER (PARTITION BY ItemNo ORDER BY Version DESC) newNotes 
  FROM tablename
)
UPDATE cte
SET Notes = newNotes
WHERE Version IS NULL AND Notes IS NULL;

See the demo.

user_104988280

Create a CTE to determine the value we need to update to, and then join that to your original table with update join syntax:

with maxVal as
(
    select row_number() over (partition by [Item No.] order by Version desc) rn
        , notes
        , [Item No.]
    from TestTable
    where Version is not null
)
update TestTable
set Notes = maxVal.Notes
from TestTable
left join maxVal
    on TestTable.[Item No.] = maxVal.[Item No.]
    and maxVal.rn = 1
where TestTable.Version is null
    and TestTable.Notes = ''
user_125712410

You can try this approach:

-- Preparing a table variable
DECLARE @table1 AS TABLE (
    id  INT IDENTITY(1,1) NOT NULL,
    VersionID INT,
    ItemNo INT,
    Note Varchar(50)
)

insert into 
    @table1 (VersionID,ItemNo,Note)
values 
    (NULL,31,''),
    (1,31,'tasty'),
    (2,31,'kinda tasty'),
    (NULL,32,''),
    (1,32,'meh'),
    (2,32,'alright'),
    (3,32,'fabulous'),
    (NULL,33,'ambivalent'),
    (1,33,'gross'),
    (2,33,'puke');

-- update section 
update up set up.Note = db.Note
from @table1 up
inner join 
(
    select a.ItemNo,a.Note
    from @table1 a
    inner join 
    (
        select itemNo,Max(VersionID) as vs
        from @table1
        where Note <> ''
        group by itemNo
    ) as b on a.ItemNo = b.ItemNo and a.VersionID = b.vs
) as db on db.ItemNo = up.ItemNo and up.Note = ''


-- result
select * from @table1
user_127015290
Related