Update large table rows within a group

  • 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

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

  • 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."

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

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