Update large table rows within a group

Question

Post reply

Update large table rows within a group

core_dw_20

SSC Enthusiast

Points: 114
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 2 (of 2 total)

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

Mike01 SSChampion Points: 11393 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: 100947 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) A socialist is someone who will give you the shirt off *someone else's* back.