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

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.

CodePudding user response：

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

CodePudding user response：

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.

CodePudding user response：

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 = ''

CodePudding user response：

You can try that appraoch :

-- 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

CodePudding user response：

You can select the maximum value for each "Item No.", then update the original table with the corresponding value where the "Notes" is NULL.

UPDATE tab 
SET [Notes] = t2.[Notes]
FROM       tab t1 
INNER JOIN (SELECT *, MAX(Version) OVER(PARTITION BY [Item No.]) AS Max_Version
            FROM tab)  t2
        ON t1.[Item No.] = t2.[Item No.]
       AND t2.[Version]  = t2.[Max_Version]
       AND t1.[Notes] IS NULL

Check the demo here.