Update large table rows within a group

Question

Post reply

Update large table rows within a group

core_dw_20

Valued Member

Points: 64
More actions
January 13, 2021 at 6:20 pm

#3832066

I have a large employee table with lots of columns and versions and one org lookup table also with some versions.

I need to update a column org_id in employee table which is all nulls now with no history.

An employee can have multiple versions records each with same empid but same or different rc_code.

table structure

Employee—

Emp_id, emp_ver_id(pk) ,as_of_date, end_date,rc_code, org_id

Emp_ver_id is identity.

Org —

Org_ver_id(pk), Org_id , Rc_code, as_of_date, end_date

org_ver_id is identity

I need to update all org_id under each employee records with the earliest rc_code/ org_id for that employee based on as_of_date which is earliest for that employee record.

problem : due to row by row operation lookup between 2 tables on rc code gives different org_id value which is not the earliest one.

sample result needed is below -

Emp_ver_id,Emp_id,org_id,as_of_date,Rc_code

1. 1 22. 01/01/2020. 01

2. 1. 22. 02/01/2020. 03

3. 1. 22 03/01/2020. 04

4. 2. 32 01/01/2019. 13

5. 2. 32 04/09/2019. 14

Viewing 3 posts - 1 through 3 (of 3 total)

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

Mike01 SSChampion Points: 11326 More actions · Answer 1

With DDL and sample data, I can only guess at using row_number over/partition clause

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ScottPletcher SSC Guru Points: 98902 More actions · Answer 2

For example, please provide us the table structure in code and sample data in code form, as INSERT statements:

IF OBJECT_ID('tempdb.dbo.#Employee') IS NOT NULL
    DROP TABLE #Employee;
CREATE TABLE #Employee (
    emp_ver_id int IDENTITY(1, 1) NOT NULL,
    emp_id int NOT NULL,
    org_id int NULL,
    as_of_date date NULL,
    rc_code char(2) NULL
    )
INSERT INTO #Employee VALUES
    ( ... ), /* !!put your sample data here!! */
    ( ... ) /*, ...*/
    

IF OBJECT_ID('tempdb.dbo.#Org') IS NOT NULL
    DROP TABLE #Org;
CREATE TABLE #Org (
    org_ver_id int IDENTITY(1, 1) NOT NULL,
    Org_id int NOT NULL,
    Rc_code char(2) NULL, 
    as_of_date date NULL, 
    end_date date NULL
    )
INSERT INTO #Org VALUES
    ( ... ), /* !!put your sample data here!! */
    ( ... ) /*, ...*/

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."