Update Column query

Question

Update Column query

kirti

Old Hand

Points: 387
More actions
October 27, 2022 at 10:14 am

#4105249

Hi All.
Need help in implementing this logic.
I have a table in below format. Currently, a new Config ID is generated for every item number- Revision combination.
I want to take the config ID of minimum(Revision) for every item number and update it per item number. Expected Config ID is desired output.
It is not necessary than revision will always start from 1. Whatever is the minimum revision for that item number we will be updating that config ID to all other revisions.

November 9, 2022 at 6:38 am

This was removed by the editor as SPAM
November 9, 2022 at 7:45 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

The windowing function FIRST_VALUE returns the first value in an ordered set. In this case PARTITION BY item_num ORDER BY revision. You could use a CTE to UPDATE the column in the original table

drop table if exists #some_table;
go
create table #some_table (
  item_num varchar(10) not null,
  revision int,
  config_id varchar(10) not null);

insert into #some_table values
('A', 1, 'C1'),
('A', 2, 'C2'),
('A', 3, 'C3'),
('B', 2, 'B1'),
('B', 3, 'B2'),
('B', 4, 'B3');

/* SELECT using FIRST_VALUE */
select *, first_value(config_id) over (partition by item_num order by revision) fv
from #some_table
order by item_num, revision;

/* CTE to UPDATE column in original table using FIRST_VALUE */
with fv_cte(item_num, revision, config_id, fv) as (
    select *, first_value(config_id) over (partition by item_num order by revision)
    from #some_table)
update fv_cte
set config_id=fv;

select * from #some_table
order by item_num, revision;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 2

The problem is simple, but it would be helpful when you post a problem like this, if you post a readily consumable date set, have a look at the code and use the method I'm using in the future.

😎

USE TEEST;
GO
SET NOCOUNT ON;
GO

DECLARE @SAMPLE TABLE
(
    ITEM_NO     CHAR(1)     NOT NULL
   ,REVISION    INT         NOT NULL
   ,CONFIG_ID   VARCHAR(5)  NOT NULL
);
INSERT INTO @SAMPLE (ITEM_NO,REVISION,CONFIG_ID)
VALUES
    ('A',1,'C1')
   ,('A',2,'C2')
   ,('A',3,'C3')
   ,('B',2,'B1')
   ,('B',3,'B2')
   ,('B',4,'B3')
   ,('C',3,'CC1')
   ,('C',6,'CC2')
;

SELECT
    SD.ITEM_NO
   ,SD.REVISION
   ,SD.CONFIG_ID
   ,FIRST_VALUE(SD.CONFIG_ID) OVER
        (
            PARTITION BY SD.ITEM_NO
            ORDER BY SD.REVISION    ASC
        ) AS EXPECTED_CID
FROM @SAMPLE    SD;

Results:

ITEM_NO REVISION    CONFIG_ID EXPECTED_CID
------- ----------- --------- ------------
A       1           C1        C1
A       2           C2        C1
A       3           C3        C1
B       2           B1        B1
B       3           B2        B1
B       4           B3        B1
C       3           CC1       CC1
C       6           CC2       CC1